package com.apes.fn.scm.retailOrder.service.tc.commission;

import com.alibaba.fastjson.JSONArray;
import com.apes.framework.jpa.Connector;
import com.apes.framework.query.ApesDao;
import com.apes.framework.query.SqlService;
import com.apes.framework.util.MapUtil;
import com.apestech.framework.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

/**
 * @Title: EmployeeCommissionDao
 * @Description:
 * @author: ZhengZeXian
 * @create: 2021/8/23 10:29
 */
@Component
public class EmployeeCommissionDao extends ApesDao {

    @Autowired
    SqlService sqlService;
    @Autowired
    private Connector connector;

    /** 销售提成 正单*/
    public List<Map> queryXSTCZD(JSONObject param){

        checkNull(param,"BILL_ID");

        String sql = "SELECT IR_TC_XSTC.NEXTVAL ID, A.DATE_DONE TJRQ, A.DATE_DONE AS SYS_RQ ,SYSDATE CREATE_DATE,A.ORDER_TYPE,\n" +
                "                       A.ID RETAIL_ORDER_ID, A.SOURCE, A.SOURCE_BUSINESS_TYPE, B.ID RETAIL_ITEM_ID,0 TCBL_JE,\n" +
                "                       A.COMPANY_ID, A.DEPT_ID, B.ORIGINAL_SALES_UNIT_PRICE YXSDJ,B.PFT_PRICE_UNIT YYHDJ, B.ASSESSMENT_COST_PRICE,\n" +
                "                       (SELECT SUM(E.PRODUCT_QTY*E.TAX_COST_PRICE) FROM ACC_VOUCHER_ITEM D, ACC_VOUCHER_INVENTORY E ,acc_voucher a\n" +
                "                        WHERE D.ORIGIN_ITEM_ID = B.ID AND D.ID=E.VOUCHER_ITEM_ID AND E.PRODUCT_ID = B.PRODUCT_ID\n" +
                "                        and a.id = d.voucher_id and a.voucher_type ='CUSTOMER_SALE') PROFIT_AMT,\n" +
                "                       (SELECT SUM(E.TAX_COST_PRICE) FROM ACC_VOUCHER_ITEM D, ACC_VOUCHER_INVENTORY E ,acc_voucher a\n" +
                "                        WHERE D.ORIGIN_ITEM_ID = B.ID AND D.ID=E.VOUCHER_ITEM_ID AND E.PRODUCT_ID = B.PRODUCT_ID\n" +
                "                        and a.id = d.voucher_id and a.voucher_type ='CUSTOMER_SALE' and rownum=1) REFER_TAX,\n" +
                "                       (SELECT A.ID FROM ACC_VOUCHER_ITEM D, ACC_VOUCHER_INVENTORY E ,acc_voucher a\n" +
                "                        WHERE D.ORIGIN_ITEM_ID = B.ID AND D.ID=E.VOUCHER_ITEM_ID AND E.PRODUCT_ID = B.PRODUCT_ID\n" +
                "                        and a.id = d.voucher_id and a.voucher_type ='CUSTOMER_SALE' and rownum=1) VOUCHER_ID,\n" +
                "                        (SELECT SUM(E.PRODUCT_QTY*E.TAX_COST_PRICE) FROM ACC_VOUCHER_ITEM D, ACC_VOUCHER_INVENTORY E,ACC_VOUCHER acc\n" +
                "                        WHERE acc.id = d.VOUCHER_ID AND D.ID=E.VOUCHER_ITEM_ID AND E.PRODUCT_ID = B.PRODUCT_ID\n" +
                "                        and A.SOURCE = acc.ORIGIN) PROFIT_AMT_RETURN,\n" +
                "                       B.PRODUCT_ID, B.PRODUCT_UOM_ID, C.CLASSIFY_ID, B.LOCK_PRICE,C.BRAND_ID,\n" +
                "                        (SELECT B.SERVE_TYPE_ID FROM MAS_SERVE_CATEGORY B, MAS_PRODUCT C\n" +
                "                              WHERE C.CLASSIFY_ID = B.CLASSIFY_ID AND C.ID =  B.PRODUCT_ID and rownum=1) SERVE_TYPE_ID,\n" +
                "                       (SELECT T.CAR_LICENSE FROM FN_CUSTOMER_EXPAND T WHERE T.ID = A.CUSTOMER_EXPAND_ID and rownum=1) PLATE_LICENSE,\n" +
                "                       B.DISCOUNT_AMOUNT,0 DSBJ,\n" +
                "                       (SELECT (SELECT T.DESCRIPTION FROM MAS_ENUMERATION T WHERE T.ID = CO.OPERATION ) FROM MAS_CORPORATE CO\n" +
                "                            WHERE CO.PARTY_ID = (SELECT T.PARTY_ID FROM MAS_PARTY_ROLE T WHERE ID = A.COMPANY_ID) and rownum=1) OPERATION,\n" +
                "                       B.PRODUCT_UOM_QTY SALE_QTY, B.PRICE_TOTAL SALE_AMT, B.MAIN_SALESMAN, B.SUB_SALESMAN,\n" +
                "                       NVL((SELECT SUM(DISCOUNT_AMOUNT) FROM RETAIL_ORDER_DISCOUNT\n" +
                "                             WHERE RETAIL_ITEM_ID = B.ID and discount_type='001598'),0) HBDK_AMT,\n" +
                "                       NVL((SELECT SUM(DI.AMOUNT) FROM RETAIL_ORDER_DISCOUNT AD,RETAIL_ORDER_DISCOUNT_ITEM DI\n" +
                "                             WHERE DI.RETAIL_DISCOUNT_ID = AD.ID\n" +
                "                               AND AD.RETAIL_ITEM_ID = B.ID\n" +
                "                               AND B.SALE_ID = A.ID\n" +
                "                               AND AD.discount_type = '001598'\n" +
                "                               AND DI.TYPE = 0),0) NEWHBDK_AMT,\n" +
                "                       NVL((SELECT SUM(DI.AMOUNT) FROM RETAIL_ORDER_DISCOUNT AD,RETAIL_ORDER_DISCOUNT_ITEM DI\n" +
                "                             WHERE DI.RETAIL_DISCOUNT_ID = AD.ID\n" +
                "                               AND AD.RETAIL_ITEM_ID = B.ID\n" +
                "                               AND B.SALE_ID = A.ID\n" +
                "                               AND AD.discount_type = '001598'\n" +
                "                               AND DI.TYPE = 1),0) NEWBB_AMT,\n" +
                "                       NVL((SELECT SUM(DISCOUNT_AMOUNT) FROM RETAIL_ORDER_DISCOUNT\n" +
                "                             WHERE RETAIL_ITEM_ID = B.ID and discount_type='001602'),0) FXDK_AMT,\n" +
                "                       NVL((SELECT SUM(COUPON_PROFIT_AMOUNT) FROM RETAIL_ORDER_DISCOUNT\n" +
                "                             WHERE RETAIL_ITEM_ID = B.ID AND COUPON_RESOURCE = '年卡销售'),0) NKDK_AMT,\n" +
                "                       NVL((SELECT SUM(COUPON_USED_QTY) FROM RETAIL_ORDER_DISCOUNT\n" +
                "                             WHERE RETAIL_ITEM_ID = B.ID AND COUPON_DISCOUNT_TYPE = 2),0) DK_QTY," +
                "                       NVL((SELECT S.OUT_SYSTEM_BUSINESS FROM SER_SERVICE_PLAN S WHERE S.ID = A.SOURCE),0) OUT_SYSTEM_BUSINESS," +
                "                       (SELECT Z.ID FROM SER_SERVICE_PLAN_ITEM Z WHERE Z.SERVICE_PLAN_ID = A.SOURCE AND Z.PRODUCT_ID = C.ID AND Z.QTY_END <> Z.SALE_QTY) SERVICE_PLAN_ITEM_ID," +
                "                       (SELECT S.PLAN_TYPE FROM SER_SERVICE_PLAN s WHERE S.ID = A.SOURCE) PLAN_TYPE" +
                "                 FROM RETAIL_ORDER A, RETAIL_ORDER_ITEM B, MAS_PRODUCT C\n" +
                "                 WHERE A.ID = B.SALE_ID AND B.PRODUCT_ID = C.ID\n" +
                "                   AND (A.CLAIM_TYPE IS NULL OR A.SCENE_ID = 'RO003')\n" +
                //"                   AND A.PACKAGE_COMMISSION = 0 " +
                "                   AND A.APP_CALC_FLAG<>1 \n " +
                "                   AND ( B.MAIN_SALESMAN IS NOT NULL OR B.SUB_SALESMAN IS NOT NULL)\n" +
                "                   AND A.STATE IN( 'done','vouched' )" +
                "                   AND A.ORDER_TYPE NOT IN('ePlus_Return','replacement','storedValue','retailReturn','vehicleInsuranceSurrender') \n" +
                "                   AND A.ID = :BILL_ID\n";
//                "                   AND A.DATE_DONE >= to_date('2022-04-01','yyyy-mm-dd')  AND A.DATE_DONE < to_date('2022-04-14','yyyy-mm-dd')\n";

        return this.find(sql,param);
    }

    /** 销售提成 负单*/
    public List<Map> queryXSTCFD(JSONObject param){
        checkNull(param,"BILL_ID");

        String sql = "SELECT IR_TC_XSTC.NEXTVAL ID,SYSDATE CREATE_DATE, A.LAST_UPDATED_STAMP TJRQ, A.LAST_UPDATED_STAMP AS SYS_RQ,A.ORDER_TYPE,\n" +
                "                       A.ID RETAIL_ORDER_ID, A.SOURCE, A.SOURCE_BUSINESS_TYPE, B.ID RETAIL_ITEM_ID,0 TCBL_JE,\n" +
                "                       A.COMPANY_ID, A.DEPT_ID, B.ORIGINAL_SALES_UNIT_PRICE YXSDJ, B.ASSESSMENT_COST_PRICE,\n" +
                "                       (SELECT SUM(E.PRODUCT_QTY*E.TAX_COST_PRICE) FROM ACC_VOUCHER_ITEM D, ACC_VOUCHER_INVENTORY E ,ACC_VOUCHER A\n" +
                "                        WHERE D.ORIGIN_ITEM_ID = B.ID AND D.ID=E.VOUCHER_ITEM_ID AND E.PRODUCT_ID = B.PRODUCT_ID\n" +
                "                        AND A.ID = D.VOUCHER_ID AND A.VOUCHER_TYPE ='CUSTOMER_SALE') PROFIT_AMT,\n" +
                "                        ABS((SELECT SUM(E.TAX_COST_PRICE) FROM ACC_VOUCHER_ITEM D, ACC_VOUCHER_INVENTORY E,ACC_VOUCHER ACC\n" +
                "                        WHERE ACC.ID = D.VOUCHER_ID AND D.ID=E.VOUCHER_ITEM_ID AND E.PRODUCT_ID = B.PRODUCT_ID\n" +
                "                        AND A.SOURCE = ACC.ORIGIN  AND D.VOUCHER_ITEM_ID IS NOT NULL)) REFER_TAX,\n" +
                "                        (SELECT SUM(E.PRODUCT_QTY*E.TAX_COST_PRICE) FROM ACC_VOUCHER_ITEM D, ACC_VOUCHER_INVENTORY E,ACC_VOUCHER ACC\n" +
                "                        WHERE ACC.ID = D.VOUCHER_ID AND D.ID=E.VOUCHER_ITEM_ID AND E.PRODUCT_ID = B.PRODUCT_ID\n" +
                "                        AND A.SOURCE = ACC.ORIGIN  AND D.VOUCHER_ITEM_ID IS NOT NULL) PROFIT_AMT_RETURN,\n" +
                "                       B.PRODUCT_ID, B.PRODUCT_UOM_ID, C.CLASSIFY_ID, B.LOCK_PRICE,C.BRAND_ID,(SELECT f.PLAN_TYPE\n" +
                "                       FROM retail_return         g,\n" +
                "                       RETAIL_ORDER          h,            \n" +
                "                       ser_service_plan_item d,\n" +
                "                       ser_service_plan_item e,\n" +
                "                       SER_SERVICE_PLAN      f\n" +
                "                       where g.id = a.id\n" +
                "                       and g.sale_id = h.id\n" +
                "                       and h.origin = f.id\n" +
                "                       and d.service_plan_id = f.id\n" +
                "                       and e.PARENT_ITEM_ID = d.id" +
                "                       and e.PRODUCT_ID = C.ID) PLAN_TYPE,\n" +
                "                        (SELECT B.SERVE_TYPE_ID FROM MAS_SERVE_CATEGORY B, MAS_PRODUCT C\n" +
                "                              WHERE C.CLASSIFY_ID = B.CLASSIFY_ID AND C.ID =  B.PRODUCT_ID AND ROWNUM=1) SERVE_TYPE_ID,\n" +
                "                       (SELECT T.CAR_LICENSE FROM FN_CUSTOMER_EXPAND T WHERE T.ID = A.CUSTOMER_EXPAND_ID AND ROWNUM=1) PLATE_LICENSE,\n" +
                "                       B.DISCOUNT_AMOUNT,B.OLD_SALE_ID,0 DSBJ,\n" +
                "                       (SELECT (SELECT T.DESCRIPTION FROM MAS_ENUMERATION T WHERE T.ID = CO.OPERATION) FROM MAS_CORPORATE CO\n" +
                "                            WHERE CO.PARTY_ID = (SELECT T.PARTY_ID FROM MAS_PARTY_ROLE T WHERE ID = A.COMPANY_ID) AND ROWNUM=1) OPERATION,\n" +
                "                       B.PRODUCT_UOM_QTY SALE_QTY, B.PRICE_TOTAL SALE_AMT, B.MAIN_SALESMAN, B.SUB_SALESMAN,\n" +
                "                       NVL((SELECT SUM(DISCOUNT_AMOUNT) FROM RETAIL_ORDER_DISCOUNT\n" +
                "                             WHERE RETAIL_ITEM_ID = B.ID AND DISCOUNT_TYPE='001598'),0) HBDK_AMT,\n" +
                "                       NVL((SELECT SUM(DI.AMOUNT) FROM RETAIL_ORDER_DISCOUNT AD,RETAIL_ORDER_DISCOUNT_ITEM DI\n" +
                "                             WHERE DI.RETAIL_DISCOUNT_ID = AD.ID\n" +
                "                               AND AD.RETAIL_ITEM_ID = B.ID\n" +
                "                               AND B.SALE_ID = A.ID\n" +
                "                               AND AD.discount_type = '001598'\n" +
                "                               AND DI.TYPE = 0),0) NEWHBDK_AMT,\n" +
                "                       NVL((SELECT SUM(DI.AMOUNT) FROM RETAIL_ORDER_DISCOUNT AD,RETAIL_ORDER_DISCOUNT_ITEM DI\n" +
                "                             WHERE DI.RETAIL_DISCOUNT_ID = AD.ID\n" +
                "                               AND AD.RETAIL_ITEM_ID = B.ID\n" +
                "                               AND B.SALE_ID = A.ID\n" +
                "                               AND AD.discount_type = '001598'\n" +
                "                               AND DI.TYPE = 1),0) NEWBB_AMT,\n" +
                "                       NVL((SELECT SUM(DISCOUNT_AMOUNT) FROM RETAIL_ORDER_DISCOUNT\n" +
                "                             WHERE RETAIL_ITEM_ID = B.ID AND DISCOUNT_TYPE='001602'),0) FXDK_AMT,\n" +
                "                       NVL((SELECT SUM(COUPON_PROFIT_AMOUNT) FROM RETAIL_ORDER_DISCOUNT\n" +
                "                             WHERE RETAIL_ITEM_ID = B.ID AND COUPON_RESOURCE = '年卡销售'),0) NKDK_AMT," +
                "                       (SELECT Z.ID FROM SER_SERVICE_PLAN_ITEM Z,RETAIL_ORDER X WHERE X.ID = A.SOURCE AND Z.SERVICE_PLAN_ID = X.SOURCE AND Z.PRODUCT_ID = C.ID AND Z.QTY_END <> Z.SALE_QTY) SERVICE_PLAN_ITEM_ID," +
                "                       NVL((SELECT S.OUT_SYSTEM_BUSINESS FROM SER_SERVICE_PLAN S WHERE S.ID = A.SOURCE),0) OUT_SYSTEM_BUSINESS\n" +
                "                 FROM RETAIL_ORDER A, (SELECT (SELECT T.ORIGINNUMBER\n" +
                "                                              FROM RETAIL_ORDER_ITEM T\n" +
                "                                             WHERE ID = A.RETAIL_ORDER_ITEM_ID) ORIGINNUMBER,\n" +
                "                                              (SELECT B.SALE_ID\n" +
                "                                              FROM RETAIL_ORDER_ITEM B\n" +
                "                                             WHERE ID = A.RETAIL_ORDER_ITEM_ID) OLD_SALE_ID,\n" +
                "                                           T.ID ,\n" +
                "                                           T.ASSESSMENT_COST_PRICE,\n" +
                "                                           A.ID RETURN_ITEM_ID,\n" +
                "                                           T.PRODUCT_ID,\n" +
                "                                           T.MAIN_SALESMAN,\n" +
                "                                           T.SUB_SALESMAN,\n" +
                "                                           T.DISCOUNT_AMOUNT,\n" +
                "                                           T.LOCK_PRICE,\n" +
                "                                           T.PRICE_TOTAL,\n" +
                "                                           T.PRODUCT_UOM_QTY,\n" +
                "                                           T.PRODUCT_UOM_ID,\n" +
                "                                           T.ORIGINAL_SALES_UNIT_PRICE,\n" +
                "                                           T.SALE_ID\n" +
                "                                      FROM RETAIL_ORDER_ITEM T, RETAIL_RETURN_ITEM A\n" +
                "                                     WHERE A.ID = T.ORIGINNUMBER) B, MAS_PRODUCT C\n" +
                "                 WHERE A.ID = B.SALE_ID AND B.PRODUCT_ID = C.ID\n" +
                //"                   AND A.PACKAGE_COMMISSION = 0\n" +
                "                   AND A.APP_CALC_FLAG<>1 \n " +
                "                   AND ( B.MAIN_SALESMAN IS NOT NULL OR B.SUB_SALESMAN IS NOT NULL)\n" +
                "                   AND (A.SOURCE_BUSINESS_TYPE = 'RetailReturn')\n" +
                "                   AND A.STATE IN( 'done','vouched' )" +
                "                   AND A.ID = :BILL_ID\n";
//                "                   AND A.DATE_DONE >= to_date('2022-04-01','yyyy-mm-dd')  AND A.DATE_DONE < to_date('2022-04-14','yyyy-mm-dd')\n";

        return this.find(sql,param);
    }

    /** 销售提成 E保养 负单*/
    public List<Map> queryXSTCFD_EBY(JSONObject param){
        checkNull(param,"BILL_ID");

        String sql = "SELECT IR_TC_XSTC.NEXTVAL ID,SYSDATE CREATE_DATE, A.LAST_UPDATED_STAMP TJRQ, A.LAST_UPDATED_STAMP AS SYS_RQ, A.ORDER_TYPE,\n" +
                "       A.ID RETAIL_ORDER_ID, A.SOURCE, A.SOURCE_BUSINESS_TYPE, B.ID RETAIL_ITEM_ID, 0 TCBL_JE, A.COMPANY_ID, A.DEPT_ID,\n" +
                "       B.ORIGINAL_SALES_UNIT_PRICE YXSDJ, B.ASSESSMENT_COST_PRICE, A.SOURCE OLD_SALE_ID,\n" +
                "       (SELECT SUM(E.PRODUCT_QTY * E.TAX_COST_PRICE)\n" +
                "           FROM ACC_VOUCHER_ITEM D, ACC_VOUCHER_INVENTORY E, ACC_VOUCHER A\n" +
                "          WHERE D.ORIGIN_ITEM_ID = B.ID\n" +
                "            AND D.ID = E.VOUCHER_ITEM_ID\n" +
                "            AND E.PRODUCT_ID = B.PRODUCT_ID\n" +
                "            AND A.ID = D.VOUCHER_ID\n" +
                "            AND A.VOUCHER_TYPE = 'CUSTOMER_SALE') PROFIT_AMT,\n" +
                "       (SELECT SUM(E.PRODUCT_QTY * E.TAX_COST_PRICE)\n" +
                "           FROM ACC_VOUCHER_ITEM D, ACC_VOUCHER_INVENTORY E, ACC_VOUCHER ACC\n" +
                "          WHERE ACC.ID = D.VOUCHER_ID\n" +
                "            AND D.ID = E.VOUCHER_ITEM_ID\n" +
                "            AND E.PRODUCT_ID = B.PRODUCT_ID\n" +
                "            AND A.SOURCE = ACC.ORIGIN\n" +
                "            AND D.VOUCHER_ITEM_ID IS NOT NULL) PROFIT_AMT_RETURN, B.PRODUCT_ID, B.PRODUCT_UOM_ID, C.CLASSIFY_ID,\n" +
                "       B.LOCK_PRICE, C.BRAND_ID,\n" +
                "       (SELECT B.SERVE_TYPE_ID\n" +
                "           FROM MAS_SERVE_CATEGORY B, MAS_PRODUCT C\n" +
                "          WHERE C.CLASSIFY_ID = B.CLASSIFY_ID\n" +
                "            AND C.ID = B.PRODUCT_ID\n" +
                "            AND ROWNUM = 1) SERVE_TYPE_ID,\n" +
                "       (SELECT T.CAR_LICENSE\n" +
                "           FROM FN_CUSTOMER_EXPAND T\n" +
                "          WHERE T.ID = A.CUSTOMER_EXPAND_ID\n" +
                "            AND ROWNUM = 1) PLATE_LICENSE, B.DISCOUNT_AMOUNT, 0 DSBJ, B.REFER_TAX,\n" +
                "       (SELECT (SELECT T.DESCRIPTION FROM MAS_ENUMERATION T WHERE T.ID = CO.OPERATION)\n" +
                "           FROM MAS_CORPORATE CO\n" +
                "          WHERE CO.PARTY_ID = (SELECT T.PARTY_ID FROM MAS_PARTY_ROLE T WHERE ID = A.COMPANY_ID)\n" +
                "            AND ROWNUM = 1) OPERATION, B.PRODUCT_UOM_QTY SALE_QTY, B.PRICE_TOTAL SALE_AMT, B.MAIN_SALESMAN,\n" +
                "       B.SUB_SALESMAN,\n" +
                "       NVL((SELECT SUM(DISCOUNT_AMOUNT)\n" +
                "              FROM RETAIL_ORDER_DISCOUNT\n" +
                "             WHERE RETAIL_ITEM_ID = B.ID\n" +
                "               AND DISCOUNT_TYPE = '001598'), 0) HBDK_AMT,\n" +
                "       NVL((SELECT SUM(DI.AMOUNT) FROM RETAIL_ORDER_DISCOUNT AD,RETAIL_ORDER_DISCOUNT_ITEM DI\n" +
                "            WHERE DI.RETAIL_DISCOUNT_ID = AD.ID\n" +
                "               AND AD.RETAIL_ITEM_ID = B.ID\n" +
                "              AND B.SALE_ID = A.ID\n" +
                "              AND AD.discount_type = '001598'\n" +
                "               AND DI.TYPE = 0),0) NEWHBDK_AMT,\n" +
                "        NVL((SELECT SUM(DI.AMOUNT) FROM RETAIL_ORDER_DISCOUNT AD,RETAIL_ORDER_DISCOUNT_ITEM DI\n" +
                "              WHERE DI.RETAIL_DISCOUNT_ID = AD.ID\n" +
                "               AND AD.RETAIL_ITEM_ID = B.ID\n" +
                "                AND B.SALE_ID = A.ID\n" +
                "               AND AD.discount_type = '001598'\n" +
                "               AND DI.TYPE = 1),0) NEWBB_AMT,\n" +
                "       NVL((SELECT SUM(DISCOUNT_AMOUNT)\n" +
                "              FROM RETAIL_ORDER_DISCOUNT\n" +
                "             WHERE RETAIL_ITEM_ID = B.ID\n" +
                "               AND DISCOUNT_TYPE = '001602'), 0) FXDK_AMT,\n" +
                "       NVL((SELECT SUM(COUPON_PROFIT_AMOUNT)\n" +
                "              FROM RETAIL_ORDER_DISCOUNT\n" +
                "             WHERE RETAIL_ITEM_ID = B.ID\n" +
                "               AND COUPON_RESOURCE = '年卡销售'), 0) NKDK_AMT,\n" +
                "       NVL((SELECT S.OUT_SYSTEM_BUSINESS FROM SER_SERVICE_PLAN S WHERE S.ID = A.SOURCE),0) OUT_SYSTEM_BUSINESS\n" +
                "  FROM RETAIL_ORDER A, RETAIL_ORDER_ITEM B, MAS_PRODUCT C\n" +
                " WHERE A.ID = B.SALE_ID\n" +
                "   AND B.PRODUCT_ID = C.ID\n" +
                //"   AND A.PACKAGE_COMMISSION = 0\n" +
                "   AND A.APP_CALC_FLAG<>1 \n " +
                "   AND (B.MAIN_SALESMAN IS NOT NULL OR B.SUB_SALESMAN IS NOT NULL)\n" +
                "   AND A.ORDER_TYPE = 'ePlus_Return'\n" +
                "   AND A.STATE IN ('done', 'vouched')\n" +
                "   AND A.ID = :BILL_ID";
//                "                   AND A.DATE_DONE >= to_date('2022-04-01','yyyy-mm-dd')  AND A.DATE_DONE < to_date('2022-04-14','yyyy-mm-dd')\n";

        return this.find(sql,param);
    }

    /** 分销销售提成 基地非定损*/
    public List<Map> queryFXXSTC_FDS(JSONObject param){
        checkNull(param,"BILL_ID");

        String sql = "SELECT IR_TC_XSTC.NEXTVAL ID,\n" +
                "                        A.DATE_DONE AS SYS_RQ,\n" +
                "                        SYSDATE CREATE_DATE,\n" +
                "                        A.DATE_DONE TJRQ,\n" +
                "                        A.ID RETAIL_ORDER_ID,\n" +
                "                        A.ORIGIN SOURCE,\n" +
                "                        B.ID RETAIL_ITEM_ID,\n" +
                "                        A.COMPANY_ID, A.DEPT_ID,\n" +
                "                        C.PRIMARY_PRICE_UNIT YXSDJ,\n" +
                "                        C.MAIN_SALER_ID ,\n" +
                "                        C.SUP_SALER_ID,\n" +
                "                        B.PRODUCT_ID,\n" +
                "                        B.PRICE_TOTAL SALE_AMT,\n" +
                "                        B.PRODUCT_UOM_QTY SALE_QTY,\n" +
                "                        B.PRODUCT_UOM_ID,\n" +
                "                        B.DISCOUNT DISCOUNT_AMOUNT,\n" +
                "                        C.FLOOR_PRICE LOCK_PRICE,\n" +
                "                        (SELECT B.SERVE_TYPE_ID\n" +
                "                           FROM MAS_SERVE_CATEGORY B, MAS_PRODUCT C\n" +
                "                          WHERE C.CLASSIFY_ID = B.CLASSIFY_ID\n" +
                "                            AND C.ID = B.PRODUCT_ID) SERVE_TYPE_ID,\n" +
                "                        (SELECT T.STORE_TYPE_NAME\n" +
                "                           FROM MAS_STORE T\n" +
                "                          WHERE T.DEPT_ID = D.BELONG_DEPT_ID) STORE_TYPE_NAME,\n" +
                "                        (SELECT T.SOURCE_BUSINESS_TYPE FROM SER_SERVICE_PLAN T WHERE T.ID = C.SERVICE_PLAN_ID )ORDER_TYPE,\n" +
                "                        (SELECT T.BRAND_ID FROM MAS_PRODUCT T WHERE T.ID = B.PRODUCT_ID )BRAND_ID,\n" +
                "                        D.ID ORDER_ID,\n" +
                "                        D.BELONG_DEPT_ID ,\n" +
                "                        C.SERVICE_PLAN_ID SERVICE_PLAN,\n" +
                "                        D.ID SERVICE_WORK_ORDER_ID,\n" +
                "                        D.ID MAINTENANCE_ORDER_ID,\n" +
                "                        C.MAIN_SALER_ID MAIN_SALESMAN,C.SUP_SALER_ID SUB_SALESMAN,\n" +
                "                        (SELECT SUM(E.PRODUCT_QTY*E.TAX_COST_PRICE) FROM ACC_VOUCHER_ITEM D, ACC_VOUCHER_INVENTORY E ,ACC_VOUCHER A\n" +
                "                        WHERE D.ORIGIN_ITEM_ID = B.ID AND D.ID=E.VOUCHER_ITEM_ID AND E.PRODUCT_ID = B.PRODUCT_ID\n" +
                "                        AND A.ID = D.VOUCHER_ID AND A.VOUCHER_TYPE ='CUSTOMER_SALE') PROFIT_AMT,\n" +
                "                        (SELECT SUM(E.TAX_COST_PRICE) FROM ACC_VOUCHER_ITEM D, ACC_VOUCHER_INVENTORY E ,ACC_VOUCHER A\n" +
                "                        WHERE D.ORIGIN_ITEM_ID = B.ID AND D.ID=E.VOUCHER_ITEM_ID AND E.PRODUCT_ID = B.PRODUCT_ID\n" +
                "                        AND A.ID = D.VOUCHER_ID AND A.VOUCHER_TYPE ='CUSTOMER_SALE') REFER_TAX,\n" +
                "                        (SELECT T.CAR_LICENSE\n" +
                "                           FROM FN_CUSTOMER_EXPAND T\n" +
                "                          WHERE T.ID = D.FN_CUSTOMER_EXPAND_ID) PLATE_LICENSE,\n" +
                "                        (SELECT T.CLASSIFY_ID FROM MAS_PRODUCT T WHERE T.ID = B.PRODUCT_ID) CLASSIFY_ID,\n" +
                "                        (SELECT SUBSTR(T.CLASSIFY_ID, 1, 1)\n" +
                "                           FROM MAS_PRODUCT T\n" +
                "                          WHERE T.ID = B.PRODUCT_ID) SUB_CLASSIFY,\n" +
                "                        (SELECT SUBSTR(T.CLASSIFY_ID, 1, 5)\n" +
                "                           FROM MAS_PRODUCT T\n" +
                "                          WHERE T.ID = B.PRODUCT_ID) SUBSTR_CLASSIFY,\n" +
                "                        (SELECT (SELECT T.DESCRIPTION\n" +
                "                                   FROM MAS_ENUMERATION T\n" +
                "                                  WHERE T.ID = CO.OPERATION)\n" +
                "                           FROM MAS_CORPORATE CO\n" +
                "                          WHERE CO.PARTY_ID = (SELECT T.PARTY_ID\n" +
                "                                                 FROM MAS_PARTY_ROLE T\n" +
                "                                                WHERE ID = A.COMPANY_ID)) OPERATION,\n" +
                "                        (SELECT T.SOURCE_BUSINESS_TYPE_NAME\n" +
                "                           FROM SER_SERVICE_PLAN T\n" +
                "                          WHERE T.ID = C.SERVICE_PLAN_ID) SOURCE_BUSINESS_TYPE_NAME,\n" +
                "                          0 DSBJ,\n" +
                "                        TO_CHAR(D.WORKER1) WORKER1,\n" +
                "                        TO_CHAR(D.WORKER2) WORKER2,\n" +
                "                        TO_CHAR(D.WORKER3) WORKER3,\n" +
                "                        TO_CHAR(D.WORKER4) WORKER4\n" +
                "                    FROM SALE_ORDER A, SALE_ORDER_ITEM B, SER_SERVICE_PLAN_ITEM C, SER_MAINTENANCE_ORDER D\n" +
                "                  WHERE A.ID = B.SALE_ID\n" +
                "                    AND A.SOURCE = C.SERVICE_PLAN_ID\n" +
                "                    AND B.ORIGIN_ITEM_ID = C.ID\n" +
                "                    AND (C.ID = D.SERVICE_PLAN_ITEM OR C.RELEVANCE_WORK_ORDER = D.ID)\n" +
                "                    AND D.STATE = 'finish'\n" +
                "                    AND A.STATE IN ('done', 'vouched')\n" +
                "                    AND C.MAIN_SALER_ID IS NOT NULL\n" +
                "                    AND ((SELECT T.SOURCE_BUSINESS_TYPE FROM SER_SERVICE_PLAN T WHERE T.ID = C.SERVICE_PLAN_ID ) IS NULL OR\n" +
                "                    (SELECT T.SOURCE_BUSINESS_TYPE FROM SER_SERVICE_PLAN T WHERE T.ID = C.SERVICE_PLAN_ID ) !='LossQuotation')\n" +
                "                    AND A.ID = :BILL_ID";
//                "                     AND A.DATE_DONE >= to_date('2022-04-01','yyyy-mm-dd')  AND A.DATE_DONE < to_date('2022-04-14','yyyy-mm-dd')\n";

        return this.find(sql,param);
    }


    /** 分销销售提成 基地非定损负单*/
    public List<Map> queryFXXSTC_FDSFD(JSONObject param){
        checkNull(param,"BILL_ID");

        String sql = "SELECT IR_TC_XSTC.NEXTVAL ID,\n" +
                "                        A.DATE_DONE AS SYS_RQ,\n" +
                "                        SYSDATE CREATE_DATE,\n" +
                "                        A.DATE_DONE TJRQ,\n" +
                "                        A.ID RETAIL_ORDER_ID,\n" +
                "                        Z.ID SOURCE,\n" +
                "                        B.ID RETAIL_ITEM_ID,\n" +
                "                        A.COMPANY_ID, A.DEPT_ID,\n" +
                "                        C.PRIMARY_PRICE_UNIT YXSDJ,\n" +
                "                        C.MAIN_SALER_ID ,\n" +
                "                        C.SUP_SALER_ID,\n" +
                "                        B.PRODUCT_ID,\n" +
                "                        (SELECT T.TCJE\n" +
                "                        FROM TC_XSETC T\n" +
                "                        WHERE T.RETAIL_ORDER_ID = A.ID\n" +
                "                        AND T.PRODUCT_ID = B.PRODUCT_ID AND ROWNUM = 1) OLD_TCJE,\n" +
                "                        B.PRICE_TOTAL OLD_SALE_AMT,\n" +
                "                        B.PRODUCT_UOM_QTY OLD_SALE_QTY,\n" +
                "                        -X.PRICE_TOTAL SALE_AMT,\n" +
                "                        -X.PRODUCT_RETURN_QTY SALE_QTY,\n" +
                "                        B.PRODUCT_UOM_ID OLD_PRODUCT_UOM_ID,\n" +
                "                        X.PRODUCT_UOM_ID PRODUCT_UOM_ID,\n" +
                "                        B.DISCOUNT DISCOUNT_AMOUNT,\n" +
                "                        -C.FLOOR_PRICE LOCK_PRICE,\n" +
                "                        (SELECT B.SERVE_TYPE_ID\n" +
                "                           FROM MAS_SERVE_CATEGORY B, MAS_PRODUCT C\n" +
                "                          WHERE C.CLASSIFY_ID = B.CLASSIFY_ID\n" +
                "                            AND C.ID = B.PRODUCT_ID) SERVE_TYPE_ID,\n" +
                "                        (SELECT T.STORE_TYPE_NAME\n" +
                "                           FROM MAS_STORE T\n" +
                "                          WHERE T.DEPT_ID = D.BELONG_DEPT_ID) STORE_TYPE_NAME,\n" +
                "                        (SELECT T.BRAND_ID FROM MAS_PRODUCT T WHERE T.ID = B.PRODUCT_ID )BRAND_ID,\n" +
                "                        (SELECT T.SOURCE_BUSINESS_TYPE FROM SER_SERVICE_PLAN T WHERE T.ID = C.SERVICE_PLAN_ID )ORDER_TYPE,\n" +
                "                        D.ID ORDER_ID,\n" +
                "                        D.BELONG_DEPT_ID ,\n" +
                "                        C.SERVICE_PLAN_ID SERVICE_PLAN,\n" +
                "                        D.ID SERVICE_WORK_ORDER_ID,\n" +
                "                        D.ID MAINTENANCE_ORDER_ID,\n" +
                "                        C.MAIN_SALER_ID MAIN_SALESMAN,C.SUP_SALER_ID SUB_SALESMAN,\n" +
                "                        (SELECT SUM(E.PRODUCT_QTY*E.TAX_COST_PRICE) FROM ACC_VOUCHER_ITEM D, ACC_VOUCHER_INVENTORY E ,ACC_VOUCHER A\n" +
                "                        WHERE D.ORIGIN_ITEM_ID = B.ID AND D.ID=E.VOUCHER_ITEM_ID AND E.PRODUCT_ID = B.PRODUCT_ID\n" +
                "                        AND A.ID = D.VOUCHER_ID AND A.VOUCHER_TYPE ='CUSTOMER_SALE') PROFIT_AMT,\n" +
                "                        (SELECT -T.REFER_TAX FROM TC_XSETC T WHERE T.RETAIL_ORDER_ID = A.ID AND T.PRODUCT_ID = B.PRODUCT_ID AND ROWNUM = 1) REFER_TAX,\n" +
                "                        (SELECT T.CAR_LICENSE\n" +
                "                           FROM FN_CUSTOMER_EXPAND T\n" +
                "                          WHERE T.ID = D.FN_CUSTOMER_EXPAND_ID) PLATE_LICENSE,\n" +
                "                        (SELECT T.CLASSIFY_ID FROM MAS_PRODUCT T WHERE T.ID = B.PRODUCT_ID) CLASSIFY_ID,\n" +
                "                        (SELECT SUBSTR(T.CLASSIFY_ID, 1, 1)\n" +
                "                           FROM MAS_PRODUCT T\n" +
                "                          WHERE T.ID = B.PRODUCT_ID) SUB_CLASSIFY,\n" +
                "                        (SELECT SUBSTR(T.CLASSIFY_ID, 1, 5)\n" +
                "                           FROM MAS_PRODUCT T\n" +
                "                          WHERE T.ID = B.PRODUCT_ID) SUBSTR_CLASSIFY,\n" +
                "                        (SELECT (SELECT T.DESCRIPTION\n" +
                "                                   FROM MAS_ENUMERATION T\n" +
                "                                  WHERE T.ID = CO.OPERATION)\n" +
                "                           FROM MAS_CORPORATE CO\n" +
                "                          WHERE CO.PARTY_ID = (SELECT T.PARTY_ID\n" +
                "                                                 FROM MAS_PARTY_ROLE T\n" +
                "                                                WHERE ID = A.COMPANY_ID)) OPERATION,\n" +
                "                        (SELECT T.SOURCE_BUSINESS_TYPE_NAME\n" +
                "                           FROM SER_SERVICE_PLAN T\n" +
                "                          WHERE T.ID = C.SERVICE_PLAN_ID) SOURCE_BUSINESS_TYPE_NAME,\n" +
                "                          0 DSBJ,\n" +
                "                        TO_CHAR(D.WORKER1) WORKER1,\n" +
                "                        TO_CHAR(D.WORKER2) WORKER2,\n" +
                "                        TO_CHAR(D.WORKER3) WORKER3,\n" +
                "                        TO_CHAR(D.WORKER4) WORKER4\n" +
                "                    FROM SALE_ORDER A, SALE_ORDER_ITEM B, SER_SERVICE_PLAN_ITEM C, SER_MAINTENANCE_ORDER D,INTERNAL_HELP_RETURN  Z,INTERNAL_HELP_RETURN_ITEM X\n" +
                "                  WHERE A.ID = B.SALE_ID\n" +
                "                    AND A.SOURCE = C.SERVICE_PLAN_ID\n" +
                "                    AND Z.ORIGIN = A.ID\n" +
                "                    AND X.RETURN_ID = Z.ID\n" +
                "                    AND X.PRODUCT_ID = B.PRODUCT_ID\n" +
                "                    AND B.ORIGIN_ITEM_ID = C.ID\n" +
                "                    AND (C.ID = D.SERVICE_PLAN_ITEM OR C.RELEVANCE_WORK_ORDER = D.ID)\n" +
                "                    AND D.STATE = 'finish'\n" +
                "                    AND A.STATE IN ('done', 'vouched')\n" +
                "                    AND C.MAIN_SALER_ID IS NOT NULL\n" +
                "                    AND ((SELECT T.SOURCE_BUSINESS_TYPE FROM SER_SERVICE_PLAN T WHERE T.ID = C.SERVICE_PLAN_ID ) IS NULL OR\n" +
                "                    (SELECT T.SOURCE_BUSINESS_TYPE FROM SER_SERVICE_PLAN T WHERE T.ID = C.SERVICE_PLAN_ID ) !='LossQuotation')\n" +
                "                    AND Z.ID = :BILL_ID";
//                "                     AND A.DATE_DONE >= to_date('2022-04-01','yyyy-mm-dd')  AND A.DATE_DONE < to_date('2022-04-14','yyyy-mm-dd')\n";

        return this.find(sql,param);
    }





    /** 分销销售提成 基地定损*/
    public List<Map> queryFXXSTC_DS(JSONObject param){
        checkNull(param,"BILL_ID");

        String sql = "SELECT IR_TC_XSTC.NEXTVAL ID,SYSDATE CREATE_DATE, A.DATE_DONE AS SYS_RQ, A.DATE_DONE TJRQ, A.ID RETAIL_ORDER_ID, A.ORIGIN SOURCE,\n" +
                "       B.ID RETAIL_ITEM_ID, A.COMPANY_ID, A.DEPT_ID, C.PRIMARY_PRICE_UNIT YXSDJ, C.MAIN_SALER_ID, C.SUP_SALER_ID,\n" +
                "       B.PRODUCT_ID, B.PRICE_TOTAL SALE_AMT, B.PRODUCT_UOM_QTY SALE_QTY, B.PRODUCT_UOM_ID,NVL(B.DISCOUNT,0) DISCOUNT_AMOUNT,\n" +
                "       C.FLOOR_PRICE LOCK_PRICE, 0 NKDK_AMT,0 FXDK_AMT ,0 HBDK_AMT,0 NEWHBDK_AMT,0 NEWBB_AMT,\n" +
                "       (SELECT B.SERVE_TYPE_ID\n" +
                "           FROM MAS_SERVE_CATEGORY B, MAS_PRODUCT C\n" +
                "          WHERE C.CLASSIFY_ID = B.CLASSIFY_ID\n" +
                "            AND C.ID = B.PRODUCT_ID\n" +
                "            AND ROWNUM = 1) SERVE_TYPE_ID,\n" +
                "       (SELECT T.STORE_TYPE_NAME\n" +
                "           FROM MAS_STORE T\n" +
                "          WHERE T.DEPT_ID = D.BELONG_DEPT_ID\n" +
                "            AND ROWNUM = 1) STORE_TYPE_NAME, D.SOURCE_BUSINESS_TYPE ORDER_TYPE, D.ID ORDER_ID, D.BELONG_DEPT_ID,\n" +
                "       C.SERVICE_PLAN_ID SERVICE_PLAN, D.ID SERVICE_WORK_ORDER_ID, D.ID MAINTENANCE_ORDER_ID,\n" +
                "       C.MAIN_SALER_ID MAIN_SALESMAN, C.SUP_SALER_ID SUB_SALESMAN,\n" +
                "       (SELECT SUM(E.PRODUCT_QTY * E.TAX_COST_PRICE)\n" +
                "           FROM ACC_VOUCHER_ITEM D, ACC_VOUCHER_INVENTORY E, ACC_VOUCHER A\n" +
                "          WHERE D.ORIGIN_ITEM_ID = B.ID\n" +
                "            AND D.ID = E.VOUCHER_ITEM_ID\n" +
                "            AND E.PRODUCT_ID = B.PRODUCT_ID\n" +
                "            AND A.ID = D.VOUCHER_ID\n" +
                "            AND A.VOUCHER_TYPE = 'CUSTOMER_SALE'\n" +
                "            AND ROWNUM = 1) PROFIT_AMT,\n" +
                "       (SELECT SUM(E.TAX_COST_PRICE)\n" +
                "           FROM ACC_VOUCHER_ITEM D, ACC_VOUCHER_INVENTORY E, ACC_VOUCHER VD\n" +
                "          WHERE D.SOURCE_ITEM_ID = B.ID\n" +
                "            AND D.ID = E.VOUCHER_ITEM_ID\n" +
                "            AND E.PRODUCT_ID = B.PRODUCT_ID\n" +
                "            AND VD.ID = D.VOUCHER_ID AND VD.COMPANY_ID = A.COMPANY_ID\n" +
                "            AND VD.VOUCHER_TYPE = 'CUSTOMER_SALE'\n" +
                "            ) REFER_TAX,\n" +
                "       (SELECT T.CAR_LICENSE\n" +
                "           FROM FN_CUSTOMER_EXPAND T\n" +
                "          WHERE T.ID = D.FN_CUSTOMER_EXPAND_ID\n" +
                "            AND ROWNUM = 1) PLATE_LICENSE,\n" +
                "                        (SELECT T.BRAND_ID FROM MAS_PRODUCT T WHERE T.ID = B.PRODUCT_ID )BRAND_ID,\n" +
                "       (SELECT T.CLASSIFY_ID\n" +
                "           FROM MAS_PRODUCT T\n" +
                "          WHERE T.ID = B.PRODUCT_ID\n" +
                "            AND ROWNUM = 1) CLASSIFY_ID,\n" +
                "       (SELECT SUBSTR(T.CLASSIFY_ID, 1, 1)\n" +
                "           FROM MAS_PRODUCT T\n" +
                "          WHERE T.ID = B.PRODUCT_ID\n" +
                "            AND ROWNUM = 1) SUB_CLASSIFY,\n" +
                "       (SELECT SUBSTR(T.CLASSIFY_ID, 1, 5)\n" +
                "           FROM MAS_PRODUCT T\n" +
                "          WHERE T.ID = B.PRODUCT_ID\n" +
                "            AND ROWNUM = 1) SUBSTR_CLASSIFY,\n" +
                "       (SELECT (SELECT T.DESCRIPTION\n" +
                "                    FROM MAS_ENUMERATION T\n" +
                "                   WHERE T.ID = CO.OPERATION\n" +
                "                     AND ROWNUM = 1)\n" +
                "           FROM MAS_CORPORATE CO\n" +
                "          WHERE CO.PARTY_ID = (SELECT T.PARTY_ID\n" +
                "                                 FROM MAS_PARTY_ROLE T\n" +
                "                                WHERE ID = A.COMPANY_ID\n" +
                "                                  AND ROWNUM = 1)) OPERATION, D.SOURCE_BUSINESS_TYPE_NAME SOURCE_BUSINESS_TYPE_NAME,\n" +
                "       D.SALE_DEPT_ID SALE_DEPT_ID, D.SERVICE_DEPT_ID SERVICE_DEPT_ID, NVL(D.OUT_SYSTEM_BUSINESS, 0) OUT_SYSTEM_BUSINESS,\n" +
                "       1 DSBJ\n" +
                "  FROM SALE_ORDER A, SALE_ORDER_ITEM B, SER_SERVICE_PLAN_ITEM C, SER_SERVICE_PLAN D\n" +
                " WHERE A.ID = B.SALE_ID\n" +
                "   AND B.ORIGIN_ITEM_ID = C.ID\n" +
                "   AND C.SERVICE_PLAN_ID = D.ID\n" +
                "   AND A.STATE IN ('done', 'vouched')\n" +
                "   AND C.MAIN_SALER_ID IS NOT NULL\n" +
                "   AND D.SOURCE_BUSINESS_TYPE = 'LossQuotation'" +
                "   AND A.ID = :BILL_ID";
//                "                    AND A.DATE_DONE >= to_date('2022-04-01','yyyy-mm-dd')  AND A.DATE_DONE < to_date('2022-04-14','yyyy-mm-dd')\n";

        return this.find(sql,param);
    }


    /** 分销销售提成 基地定损负单*/
    public List<Map> queryFXXSTC_DSFD(JSONObject param){
        checkNull(param,"BILL_ID");

        String sql = "SELECT IR_TC_XSTC.NEXTVAL ID,SYSDATE CREATE_DATE, A.DATE_DONE AS SYS_RQ, A.DATE_DONE TJRQ, A.ID RETAIL_ORDER_ID,Z.ID SOURCE,\n" +
                "       B.ID RETAIL_ITEM_ID, A.COMPANY_ID, A.DEPT_ID, C.PRIMARY_PRICE_UNIT YXSDJ, C.MAIN_SALER_ID, C.SUP_SALER_ID,\n" +
                "       B.PRODUCT_ID, B.PRICE_TOTAL OLD_SALE_AMT, -X.PRICE_TOTAL SALE_AMT,B.PRODUCT_UOM_QTY OLD_SALE_QTY,-X.PRODUCT_RETURN_QTY SALE_QTY, B.PRODUCT_UOM_ID OLD_PRODUCT_UOM_ID,X.PRODUCT_UOM_ID PRODUCT_UOM_ID,NVL(B.DISCOUNT,0) DISCOUNT_AMOUNT,\n" +
                "       -C.FLOOR_PRICE LOCK_PRICE, 0 NKDK_AMT,0 FXDK_AMT ,0 HBDK_AMT,0 NEWHBDK_AMT,0 NEWBB_AMT,\n" +
                "       (SELECT B.SERVE_TYPE_ID\n" +
                "           FROM MAS_SERVE_CATEGORY B, MAS_PRODUCT C\n" +
                "          WHERE C.CLASSIFY_ID = B.CLASSIFY_ID\n" +
                "            AND C.ID = B.PRODUCT_ID\n" +
                "            AND ROWNUM = 1) SERVE_TYPE_ID,\n" +
                "            (SELECT T.TCJE\n" +
                "             FROM TC_XSETC T\n" +
                "             WHERE T.RETAIL_ORDER_ID = A.ID\n" +
                "             AND T.PRODUCT_ID = B.PRODUCT_ID AND ROWNUM = 1) OLD_TCJE,\n" +
                "       (SELECT T.STORE_TYPE_NAME\n" +
                "           FROM MAS_STORE T\n" +
                "          WHERE T.DEPT_ID = D.BELONG_DEPT_ID\n" +
                "            AND ROWNUM = 1) STORE_TYPE_NAME, D.SOURCE_BUSINESS_TYPE ORDER_TYPE, D.ID ORDER_ID, D.BELONG_DEPT_ID,\n" +
                "       C.SERVICE_PLAN_ID SERVICE_PLAN, D.ID SERVICE_WORK_ORDER_ID, D.ID MAINTENANCE_ORDER_ID,\n" +
                "       C.MAIN_SALER_ID MAIN_SALESMAN, C.SUP_SALER_ID SUB_SALESMAN,\n" +
                "       (SELECT SUM(E.PRODUCT_QTY * E.TAX_COST_PRICE)\n" +
                "           FROM ACC_VOUCHER_ITEM D, ACC_VOUCHER_INVENTORY E, ACC_VOUCHER A\n" +
                "          WHERE D.ORIGIN_ITEM_ID = B.ID\n" +
                "            AND D.ID = E.VOUCHER_ITEM_ID\n" +
                "            AND E.PRODUCT_ID = B.PRODUCT_ID\n" +
                "            AND A.ID = D.VOUCHER_ID\n" +
                "            AND A.VOUCHER_TYPE = 'CUSTOMER_SALE'\n" +
                "            AND ROWNUM = 1) PROFIT_AMT,\n" +
                "           (SELECT -T.REFER_TAX FROM TC_XSETC T WHERE T.RETAIL_ORDER_ID = A.ID AND T.PRODUCT_ID = B.PRODUCT_ID AND ROWNUM = 1) REFER_TAX,\n" +
                "       (SELECT T.CAR_LICENSE\n" +
                "           FROM FN_CUSTOMER_EXPAND T\n" +
                "          WHERE T.ID = D.FN_CUSTOMER_EXPAND_ID\n" +
                "            AND ROWNUM = 1) PLATE_LICENSE,\n" +
                "                        (SELECT T.BRAND_ID FROM MAS_PRODUCT T WHERE T.ID = B.PRODUCT_ID )BRAND_ID,\n" +
                "       (SELECT T.CLASSIFY_ID\n" +
                "           FROM MAS_PRODUCT T\n" +
                "          WHERE T.ID = B.PRODUCT_ID\n" +
                "            AND ROWNUM = 1) CLASSIFY_ID,\n" +
                "       (SELECT SUBSTR(T.CLASSIFY_ID, 1, 1)\n" +
                "           FROM MAS_PRODUCT T\n" +
                "          WHERE T.ID = B.PRODUCT_ID\n" +
                "            AND ROWNUM = 1) SUB_CLASSIFY,\n" +
                "       (SELECT SUBSTR(T.CLASSIFY_ID, 1, 5)\n" +
                "           FROM MAS_PRODUCT T\n" +
                "          WHERE T.ID = B.PRODUCT_ID\n" +
                "            AND ROWNUM = 1) SUBSTR_CLASSIFY,\n" +
                "       (SELECT (SELECT T.DESCRIPTION\n" +
                "                    FROM MAS_ENUMERATION T\n" +
                "                   WHERE T.ID = CO.OPERATION\n" +
                "                     AND ROWNUM = 1)\n" +
                "           FROM MAS_CORPORATE CO\n" +
                "          WHERE CO.PARTY_ID = (SELECT T.PARTY_ID\n" +
                "                                 FROM MAS_PARTY_ROLE T\n" +
                "                                WHERE ID = A.COMPANY_ID\n" +
                "                                  AND ROWNUM = 1)) OPERATION, D.SOURCE_BUSINESS_TYPE_NAME SOURCE_BUSINESS_TYPE_NAME,\n" +
                "       D.SALE_DEPT_ID SALE_DEPT_ID, D.SERVICE_DEPT_ID SERVICE_DEPT_ID, NVL(D.OUT_SYSTEM_BUSINESS, 0) OUT_SYSTEM_BUSINESS,\n" +
                "       1 DSBJ\n" +
                "  FROM SALE_ORDER A, SALE_ORDER_ITEM B, SER_SERVICE_PLAN_ITEM C, SER_SERVICE_PLAN D,INTERNAL_HELP_RETURN  Z,INTERNAL_HELP_RETURN_ITEM X\n" +
                " WHERE A.ID = B.SALE_ID\n" +
                "   AND B.ORIGIN_ITEM_ID = C.ID\n" +
                "   AND Z.ORIGIN = A.ID\n" +
                "   AND X.RETURN_ID = Z.ID\n" +
                "   AND X.PRODUCT_ID = B.PRODUCT_ID\n" +
                "   AND C.SERVICE_PLAN_ID = D.ID\n" +
                "   AND A.STATE IN ('done', 'vouched')\n" +
                "   AND C.MAIN_SALER_ID IS NOT NULL\n" +
                "   AND D.SOURCE_BUSINESS_TYPE = 'LossQuotation'" +
                "   AND Z.ID = :BILL_ID";
//                "                    AND A.DATE_DONE >= to_date('2022-04-01','yyyy-mm-dd')  AND A.DATE_DONE < to_date('2022-04-14','yyyy-mm-dd')\n";

        return this.find(sql,param);
    }


    /**销售提成负单 原提成金额*/
    public JSONArray queryXSTCFDOLDTCJE(JSONObject param){
        checkNull(param,"OLD_SALE_ID","DEPT_ID","PRODUCT_ID");

        String sql = "SELECT T.TCJE AS OLE_TCJE FROM TC_XSETC T WHERE T.RETAIL_ORDER_ID = :OLD_SALE_ID AND T.DEPT_ID = :DEPT_ID AND T.PRODUCT_ID = :PRODUCT_ID";
        return toJSONArray(this.find(sql, param));
    }

    /**销售提成负单 原销售金额*/
    public JSONArray queryXSTCFDYXXJE(JSONObject param){
        checkNull(param,"OLD_SALE_ID","DEPT_ID","PRODUCT_ID");

        String sql = "SELECT T.SALE_AMT AS OLE_SALE_AMT FROM TC_XSETC T WHERE T.RETAIL_ORDER_ID = :OLD_SALE_ID AND T.DEPT_ID = :DEPT_ID AND T.PRODUCT_ID = :PRODUCT_ID";
        return toJSONArray(this.find(sql, param));
    }

    /**年卡套包提成 有套包提成*/
    public List<Map> queryNKTC_YTB(JSONObject param){
        checkNull(param,"BILL_ID");

        String sql = "SELECT IR_TC_NKTC.NEXTVAL ID,SYSDATE CREATE_DATE, A.DATE_DONE TJRQ, A.DATE_DONE AS SYS_RQ, A.ID RETAIL_ORDER_ID, A.SOURCE,\n" +
                "       A.SOURCE_BUSINESS_TYPE, A.COMPANY_ID, A.DEPT_ID,\n" +
                "       (SELECT T.NAME\n" +
                "           FROM MAS_PARTY T, MAS_PARTY_ROLE A\n" +
                "          WHERE A.PARTY_ID = T.ID\n" +
                "            AND A.ID = A.COMPANY_ID) COMPANY_NAME,\n" +
                "       \n" +
                "       (SELECT T.NAME FROM MAS_DEPT T WHERE T.ID = A.DEPT_ID) DEPT_NAME,\n" +
                "       (SELECT T.CAR_LICENSE FROM FN_CUSTOMER_EXPAND T WHERE T.ID = A.CUSTOMER_EXPAND_ID and rownum=1) PLATE_LICENSE,\n" +
                "       (SELECT B.ID\n" +
                "           FROM RETAIL_ORDER_ITEM B\n" +
                "          WHERE B.SALE_ID = A.ID\n" +
                "            AND ROWNUM = 1) RETAIL_ITEM_ID, A.ORDER_TYPE, A.AMT_TOTAL SALE_AMT, A.PACKAGE_COMMISSION TC_AMT,\n" +
                "       (SELECT T.MAIN_SALESMAN\n" +
                "           FROM RETAIL_ORDER_ITEM T\n" +
                "          WHERE T.SALE_ID = A.ID\n" +
                "            AND ROWNUM = 1) SALESMAN,\n" +
                "       (SELECT T.MAIN_SALESMAN\n" +
                "           FROM RETAIL_ORDER_ITEM T\n" +
                "          WHERE T.SALE_ID = A.ID\n" +
                "            AND ROWNUM = 1) EMPLOYEE_ID,\n" +
                "       (SELECT T.SUB_SALESMAN\n" +
                "           FROM RETAIL_ORDER_ITEM T\n" +
                "          WHERE T.SALE_ID = A.ID\n" +
                "            AND ROWNUM = 1) SUB_SALESMAN,\n" +
                "       (SELECT T.SUB_SALESMAN\n" +
                "           FROM RETAIL_ORDER_ITEM T\n" +
                "          WHERE T.SALE_ID = A.ID\n" +
                "            AND ROWNUM = 1) SUB_EMPLOYEE_ID\n" +
                "  FROM RETAIL_ORDER A\n" +
                " WHERE (A.ORDER_TYPE = 'yearcard' OR A.ORDER_TYPE = 'yearCardReturn' OR A.ORDER_TYPE = 'storedValue')\n" +
                "   AND A.PACKAGE_COMMISSION <> 0\n" +
                "   AND ((SELECT T.MAIN_SALESMAN\n" +
                "           FROM RETAIL_ORDER_ITEM T\n" +
                "          WHERE T.SALE_ID = A.ID\n" +
                "            AND ROWNUM = 1) IS NOT NULL OR (SELECT T.SUB_SALESMAN\n" +
                "           FROM RETAIL_ORDER_ITEM T\n" +
                "          WHERE T.SALE_ID = A.ID\n" +
                "            AND ROWNUM = 1) IS NOT NULL)\n" +
                "   AND A.ID = :BILL_ID";
//                "   AND A.DATE_DONE >= to_date('2021-08-19','yyyy-mm-dd')  AND A.DATE_DONE < to_date('2021-09-01','yyyy-mm-dd')\n";

        return this.find(sql,param);
    }

    /**年卡套包提成 无套包提成*/
    public List<Map> queryNKTC_WTB(JSONObject param){
        checkNull(param,"BILL_ID");

        String sql = "SELECT IR_TC_NKTC.NEXTVAL ID,SYSDATE CREATE_DATE, A.DATE_DONE TJRQ,A.DATE_DONE AS SYS_RQ,A.ID RETAIL_ORDER_ID,\n" +
                "                            A.SOURCE,A.SOURCE_BUSINESS_TYPE,A.COMPANY_ID,A.DEPT_ID,A.ORDER_TYPE,A.PACKAGE_COMMISSION TC_AMT,\n" +
                "                            B.ORIGINAL_SALES_UNIT_PRICE YXSDJ, B.ASSESSMENT_COST_PRICE,\n" +
                "                            (SELECT T.NAME FROM MAS_PARTY T, MAS_PARTY_ROLE A WHERE A.PARTY_ID = T.ID AND A.ID = A.COMPANY_ID) COMPANY_NAME,\n" +
                "                            (SELECT T.NAME FROM MAS_DEPT T WHERE T.ID = A.DEPT_ID)DEPT_NAME,\n" +
                "                            (SELECT B.ID FROM RETAIL_ORDER_ITEM B WHERE B.SALE_ID = A.ID AND ROWNUM = 1) RETAIL_ITEM_ID,\n" +
                "                            (SELECT SUM(E.PRODUCT_QTY*E.TAX_COST_PRICE) FROM ACC_VOUCHER_ITEM D, ACC_VOUCHER_INVENTORY E ,ACC_VOUCHER A\n" +
                "                            WHERE D.ORIGIN_ITEM_ID = B.ID AND D.ID=E.VOUCHER_ITEM_ID AND E.PRODUCT_ID = B.PRODUCT_ID\n" +
                "                            AND A.ID = D.VOUCHER_ID AND A.VOUCHER_TYPE ='CUSTOMER_SALE') PROFIT_AMT,\n" +
                "                            B.PRODUCT_ID, B.PRODUCT_UOM_ID, C.CLASSIFY_ID,B.LOCK_PRICE,B.DISCOUNT_AMOUNT,0 DSBJ,B.REFER_TAX,\n" +
                "                            B.PRODUCT_UOM_QTY SALE_QTY, B.MAIN_SALESMAN, B.PRICE_TOTAL SALE_AMT,\n" +
                "                            (SELECT B.SERVE_TYPE_ID FROM MAS_SERVE_CATEGORY B, MAS_PRODUCT C\n" +
                "                            WHERE C.CLASSIFY_ID = B.CLASSIFY_ID AND C.ID =  B.PRODUCT_ID AND ROWNUM=1) SERVE_TYPE_ID,\n" +
                "                            (SELECT T.CAR_LICENSE FROM FN_CUSTOMER_EXPAND T WHERE T.ID = A.CUSTOMER_EXPAND_ID AND ROWNUM=1) PLATE_LICENSE,\n" +
                "                            (SELECT T.MAIN_SALESMAN FROM RETAIL_ORDER_ITEM T WHERE T.SALE_ID = A.ID AND ROWNUM=1 )SALESMAN,\n" +
                "                            (SELECT T.MAIN_SALESMAN FROM RETAIL_ORDER_ITEM T WHERE T.SALE_ID = A.ID AND ROWNUM=1 )EMPLOYEE_ID,\n" +
                "                            (SELECT T.SUB_SALESMAN FROM RETAIL_ORDER_ITEM T WHERE T.SALE_ID = A.ID AND ROWNUM=1 )SUB_SALESMAN,\n" +
                "                            (SELECT T.SUB_SALESMAN FROM RETAIL_ORDER_ITEM T WHERE T.SALE_ID = A.ID AND ROWNUM=1 )SUB_EMPLOYEE_ID,\n" +
                "                            NVL((SELECT SUM(DISCOUNT_AMOUNT) FROM RETAIL_ORDER_DISCOUNT\n" +
                "                                 WHERE RETAIL_ITEM_ID = B.ID AND DISCOUNT_TYPE='001598'),0) HBDK_AMT,\n" +
                "                            NVL((SELECT SUM(DI.AMOUNT) FROM RETAIL_ORDER_DISCOUNT AD,RETAIL_ORDER_DISCOUNT_ITEM DI\n" +
                "                             WHERE DI.RETAIL_DISCOUNT_ID = AD.ID\n" +
                "                               AND AD.RETAIL_ITEM_ID = B.ID\n" +
                "                               AND B.SALE_ID = A.ID\n" +
                "                               AND AD.discount_type = '001598'\n" +
                "                               AND DI.TYPE = 0),0) NEWHBDK_AMT,\n" +
                "                            NVL((SELECT SUM(DI.AMOUNT) FROM RETAIL_ORDER_DISCOUNT AD,RETAIL_ORDER_DISCOUNT_ITEM DI\n" +
                "                             WHERE DI.RETAIL_DISCOUNT_ID = AD.ID\n" +
                "                               AND AD.RETAIL_ITEM_ID = B.ID\n" +
                "                               AND B.SALE_ID = A.ID\n" +
                "                               AND AD.discount_type = '001598'\n" +
                "                               AND DI.TYPE = 1),0) NEWBB_AMT,\n" +
                "                            NVL((SELECT SUM(DISCOUNT_AMOUNT) FROM RETAIL_ORDER_DISCOUNT\n" +
                "                                 WHERE RETAIL_ITEM_ID = B.ID AND DISCOUNT_TYPE='001602'),0) FXDK_AMT,\n" +
                "                            NVL((SELECT SUM(COUPON_PROFIT_AMOUNT) FROM RETAIL_ORDER_DISCOUNT\n" +
                "                                 WHERE RETAIL_ITEM_ID = B.ID AND COUPON_RESOURCE = '年卡销售'),0) NKDK_AMT\n" +
                "                       FROM RETAIL_ORDER A,RETAIL_ORDER_ITEM B,MAS_PRODUCT C\n" +
                "                      WHERE (A.ORDER_TYPE = 'yearcard' OR A.ORDER_TYPE = 'yearCardReturn')\n" +
                "                        AND A.PACKAGE_COMMISSION = 0 AND A.ID = B.SALE_ID AND B.PRODUCT_ID = C.ID AND A.STATE IN( 'done','vouched')\n" +
                "                        AND A.ID = :BILL_ID";
//                "                        AND A.DATE_DONE >= to_date('2021-08-19','yyyy-mm-dd')  AND A.DATE_DONE < to_date('2021-09-01','yyyy-mm-dd')\n";

        return this.find(sql,param);
    }

    /**年卡提成无套包 原提成金额*/
    public JSONArray queryNKTCWTBOLDTCJE(JSONObject param){
        checkNull(param,"SOURCE","DEPT_ID","PRODUCT_ID");

        String sql = " SELECT T.TCJE AS OLE_TCJE FROM TC_XSETC T WHERE T.RETAIL_ORDER_ID = :SOURCE AND T.DEPT_ID = :DEPT_ID AND T.PRODUCT_ID = :PRODUCT_ID";
        return toJSONArray(this.find(sql, param));
    }

    /**年卡提成无套包 原销售金额*/
    public JSONArray queryNKTCWTBOLDXSJE(JSONObject param){
        checkNull(param,"SOURCE","DEPT_ID","PRODUCT_ID");

        String sql = " SELECT T.SALE_AMT AS OLE_XSJE FROM TC_XSETC T WHERE T.RETAIL_ORDER_ID = :SOURCE AND T.DEPT_ID = :DEPT_ID AND T.PRODUCT_ID = :PRODUCT_ID";
        return toJSONArray(this.find(sql, param));
    }

    /**接送车提成 接车人提成*/
    public List<Map> queryJSCTC_JC(JSONObject param){
        checkNull(param,"BILL_ID");

        String sql = "SELECT IR_TC_JSCTC.NEXTVAL ID,SYSDATE CREATE_DATE,C.DISPATCHER_DEPT_ID,\n" +
                "                      (SELECT T.OPERATE_DATE FROM FN_INNER_PICK_DEL_UP_CAR_ITEM T WHERE T.INNER_PICK_DELIVERY_UP_CAR = B.ID AND T.NODE_DESCRIPTION = 'confirm')TJRQ,\n" +
                "                      GET_DATE(C.OPERATE_DATE) JCRQ, C.OPERATE_DATE AS SYS_RQ, b.ID FN_INNER_PICK_DEL_UP_CAR_ID,\n" +
                "                           (SELECT T.CAR_LICENSE FROM FN_CUSTOMER_EXPAND T WHERE T.ID = B.FN_CUSTOMER_EXPAND_ID) PLATE_LICENSE,\n" +
                "                           (SELECT T.COMPANY_ID FROM MAS_DEPT T WHERE T.ID = C.DISPATCHER_DEPT_ID) COMPANY_ID,\n" +
                "                           B.BELONG_DEPT_ID DEPT_ID_MD, B.ORDER_TYPE ORDER_TYPE, B.OTHER_SIDE_DEPT_ID DEPT_ID_JD, C.CHAUFFEUR JSCR, '交车' BZ,C.COMMISSION_FLAG\n" +
                "                      FROM  FN_INNER_PICK_DEL_UP_CAR B, FN_INNER_PICK_DEL_UP_CAR_ITEM C\n" +
                "                     WHERE  B.ID = C.INNER_PICK_DELIVERY_UP_CAR AND C.NODE_DESCRIPTION = 'create'\n" +
                "                      AND B.STATE = 'confirm'\n" +
                "                      AND C.CHAUFFEUR IS NOT NULL\n" +
                "                      AND B.ID = :BILL_ID";
//                "                      AND (SELECT T.OPERATE_DATE FROM FN_INNER_PICK_DEL_UP_CAR_ITEM T WHERE T.INNER_PICK_DELIVERY_UP_CAR = B.ID AND T.NODE_DESCRIPTION = 'confirm') >= to_date('2021-08-19','yyyy-mm-dd')" +
//                "                      AND (SELECT T.OPERATE_DATE FROM FN_INNER_PICK_DEL_UP_CAR_ITEM T WHERE T.INNER_PICK_DELIVERY_UP_CAR = B.ID AND T.NODE_DESCRIPTION = 'confirm') < to_date('2021-09-01','yyyy-mm-dd')" ;

        return this.find(sql,param);
    }

    /**接送车提成 (归还)送车人提成*/
    public List<Map> queryJSCTC_SC(JSONObject param){
        checkNull(param,"BILL_ID");

        //查询内协交接车单
        String sql = " SELECT IR_TC_JSCTC.NEXTVAL ID,SYSDATE CREATE_DATE,C.DISPATCHER_DEPT_ID,\n" +
                "                      (SELECT T.OPERATE_DATE FROM FN_INNER_PICK_DEL_UP_CAR_ITEM T WHERE T.INNER_PICK_DELIVERY_UP_CAR = B.ID AND T.NODE_DESCRIPTION = 'confirm')TJRQ,\n" +
                "                      GET_DATE(C.OPERATE_DATE) JCRQ,  C.OPERATE_DATE AS SYS_RQ, b.ID FN_INNER_PICK_DEL_UP_CAR_ID,\n" +
                "                           (SELECT T.CAR_LICENSE FROM FN_CUSTOMER_EXPAND T WHERE T.ID = B.FN_CUSTOMER_EXPAND_ID) PLATE_LICENSE,\n" +
                "                           (SELECT T.COMPANY_ID FROM MAS_DEPT T WHERE T.ID = C.DISPATCHER_DEPT_ID)COMPANY_ID,\n" +
                "                           B.BELONG_DEPT_ID DEPT_ID_MD,B.ORDER_TYPE ORDER_TYPE,B.OTHER_SIDE_DEPT_ID DEPT_ID_JD, C.CHAUFFEUR JSCR, '归还' BZ , C.COMMISSION_FLAG\n" +
                "                      FROM  FN_INNER_PICK_DEL_UP_CAR B, FN_INNER_PICK_DEL_UP_CAR_ITEM C\n" +
                "                     WHERE B.ID = C.INNER_PICK_DELIVERY_UP_CAR\n" +
                "                       AND C.NODE_DESCRIPTION = 'return'\n" +
                "                       AND B.STATE = 'confirm'\n" +
                "                       AND C.CHAUFFEUR IS NOT NULL\n" +
                "                       AND B.ID = :BILL_ID";
//                "                      AND (SELECT T.OPERATE_DATE FROM FN_INNER_PICK_DEL_UP_CAR_ITEM T WHERE T.INNER_PICK_DELIVERY_UP_CAR = B.ID AND T.NODE_DESCRIPTION = 'confirm') >= to_date('2021-08-19','yyyy-mm-dd')" +
//                "                      AND (SELECT T.OPERATE_DATE FROM FN_INNER_PICK_DEL_UP_CAR_ITEM T WHERE T.INNER_PICK_DELIVERY_UP_CAR = B.ID AND T.NODE_DESCRIPTION = 'confirm') < to_date('2021-09-01','yyyy-mm-dd')" ;
        return this.find(sql,param);
    }

    /**内协开单提成 */
    public List<Map> queryNXKDTC(JSONObject param){
        checkNull(param,"BILL_ID");

        //查询内协交接车单
        String sql = "SELECT IR_TC_KDTC.NEXTVAL ID,SYSDATE CREATE_DATE,'NX_KD_TC' TYPE,\n" +
                "                      (SELECT T.OPERATE_DATE FROM FN_INNER_PICK_DEL_UP_CAR_ITEM T WHERE T.INNER_PICK_DELIVERY_UP_CAR = B.ID AND T.NODE_DESCRIPTION = 'confirm')TJRQ,\n" +
                "                      GET_DATE(C.OPERATE_DATE) JCRQ,  B.ID FN_INNER_PICK_DEL_UP_CAR_ID,\n" +
                "                           (SELECT T.CAR_LICENSE FROM FN_CUSTOMER_EXPAND T WHERE T.ID = B.FN_CUSTOMER_EXPAND_ID) PLATE_LICENSE,\n" +
                "                           (SELECT T.COMPANY_ID FROM MAS_DEPT T WHERE T.ID = B.OTHER_SIDE_DEPT_ID)COMPANY_ID, \n" +
                "                           B.FN_CUSTOMER_EXPAND_ID,B.ORDER_TYPE ORDER_TYPE,\n" +
                "                           B.OTHER_SIDE_DEPT_ID DEPT_ID, C.OPERATE_UID BILL_MAN\n" +
                "                      FROM FN_INNER_PICK_DEL_UP_CAR B, FN_INNER_PICK_DEL_UP_CAR_ITEM C\n" +
                "                     WHERE B.ID = C.INNER_PICK_DELIVERY_UP_CAR\n" +
                "                       AND C.NODE_DESCRIPTION = 'get'\n" +
                "                       AND B.STATE = 'confirm'\n" +
                "                       AND B.ID = :BILL_ID";

//                "                      AND (SELECT T.OPERATE_DATE FROM FN_INNER_PICK_DEL_UP_CAR_ITEM T WHERE T.INNER_PICK_DELIVERY_UP_CAR = B.ID AND T.NODE_DESCRIPTION = 'confirm') >= to_date('2021-08-19','yyyy-mm-dd')" +
//                "                      AND (SELECT T.OPERATE_DATE FROM FN_INNER_PICK_DEL_UP_CAR_ITEM T WHERE T.INNER_PICK_DELIVERY_UP_CAR = B.ID AND T.NODE_DESCRIPTION = 'confirm') < to_date('2021-09-01','yyyy-mm-dd')" ;
        return this.find(sql,param);
    }

    /**异业开单提成_零售正单*/
    public List<Map> queryYYKDTC_ZD(JSONObject param){
        checkNull(param,"BILL_ID");

        //查询零售单
        String sql = "SELECT IR_TC_KDTC.NEXTVAL ID,'YY_KD_TC' TYPE,SYSDATE CREATE_DATE,B.* FROM (SELECT A.ID RETAIL_ORDER_ID, A.DATE_DONE TJRQ, A.DATE_DONE AS SYS_RQ,A.DATE_APPROVE, A.SOURCE SOURCE, A.DEPT_ID,\n" +
                "                            D.CAR_LICENSE PLATE_LICENSE,D.MOBILE,A.COMPANY_ID,F.COMPANY_ID CROSS_COMPANY_ID,E.CROSSIC_ID CROSS_ID,\n" +
                "                            (SELECT Z.CROSS_ID FROM MAS_CROSS_IC Z WHERE Z.ID = C.CROSSIC_ID AND ROWNUM = 1) P_CROSS_ID,\n" +
                "                            (SELECT S.VERIFICATION_UID FROM SER_SERVICE_PLAN S WHERE S.ID = A.SOURCE) BILL_MAN,\n" +
                "                            (SELECT M.SERVE_TYPE_ID FROM MAS_SERVE_CATEGORY M WHERE M.CLASSIFY_ID = (SELECT T.CLASSIFY_ID FROM MAS_PRODUCT T WHERE T.ID = B.PRODUCT_ID)) SERVE_TYPE_ID\n" +
                "                    FROM RETAIL_ORDER A\n" +
                "                    LEFT JOIN RETAIL_ORDER_ITEM B\n" +
                "                    ON A.ID = B.SALE_ID\n" +
                "                    LEFT JOIN SER_SERVICE_PLAN_ITEM C\n" +
                "                    ON B.ORIGINNUMBER = C.ID\n" +
                "                    LEFT JOIN FN_CUSTOMER_EXPAND D\n" +
                "                    ON D.ID = A.CUSTOMER_EXPAND_ID\n" +
                "                    LEFT JOIN RETAIL_ORDER_DISCOUNT E\n" +
                "                    ON E.RETAIL_ITEM_ID = B.ID\n" +
                "                    LEFT JOIN MAS_CROSS_IC F ON C.CROSSIC_ID = F.ID\n" +
                "                    WHERE A.ORDER_TYPE = 'server'\n" +
                "                    AND c.CROSSIC_ID IS NOT NULL\n" +
                "                    AND C.QTY_EXCHANCE_CODE <> 0\n" +
                "                    AND A.ID = :BILL_ID\n" +
//                "                    AND A.DATE_DONE >= to_date('2021-08-19','yyyy-mm-dd')  AND A.DATE_DONE < to_date('2021-09-01','yyyy-mm-dd')\n" +
                "                   )B";

        return this.find(sql,param);
    }

    /**异业开单提成_零售负单*/
    public List<Map> queryYYKDTC_FD(JSONObject param){
        checkNull(param,"BILL_ID");

        //查询零售单
        String sql = "SELECT IR_TC_KDTC.NEXTVAL ID,'YY_KD_TC' TYPE,SYSDATE CREATE_DATE, A.ID RETAIL_ORDER_ID, A.DATE_DONE TJRQ, A.DATE_DONE AS SYS_RQ,A.DATE_APPROVE, A.SOURCE SOURCE, A.DEPT_ID,\n" +
                "                           D.CAR_LICENSE PLATE_LICENSE,D.MOBILE,A.COMPANY_ID,F.COMPANY_ID CROSS_COMPANY_ID,E.CROSSIC_ID CROSS_ID,G.SALE_ID OLD_RETAIL_ORDER_ID,\n" +
                "                           (SELECT Z.CROSS_ID FROM MAS_CROSS_IC Z WHERE Z.ID = C.CROSSIC_ID AND ROWNUM = 1) P_CROSS_ID,\n" +
                "                            (SELECT S.VERIFICATION_UID FROM SER_SERVICE_PLAN S WHERE S.ID = (SELECT F.SOURCE FROM RETAIL_ORDER F WHERE F.ID = G.SALE_ID)) BILL_MAN,\n" +
                "                           (SELECT M.SERVE_TYPE_ID FROM MAS_SERVE_CATEGORY M WHERE M.CLASSIFY_ID = (SELECT T.CLASSIFY_ID FROM MAS_PRODUCT T WHERE T.ID = B.PRODUCT_ID)) SERVE_TYPE_ID\n" +
                "                    FROM RETAIL_ORDER A\n" +
                "                    LEFT JOIN RETAIL_RETURN G\n" +
                "                    ON G.ID = A.SOURCE\n" +
                "                    LEFT JOIN RETAIL_ORDER_ITEM B\n" +
                "                    ON G.SALE_ID = B.SALE_ID\n" +
                "                    LEFT JOIN SER_SERVICE_PLAN_ITEM C\n" +
                "                    ON B.ORIGINNUMBER = C.ID\n" +
                "                    LEFT JOIN FN_CUSTOMER_EXPAND D\n" +
                "                    ON D.ID = A.CUSTOMER_EXPAND_ID\n" +
                "                    LEFT JOIN RETAIL_ORDER_DISCOUNT E\n" +
                "                    ON E.RETAIL_ITEM_ID = B.ID\n" +
                "                    LEFT JOIN MAS_CROSS_IC F ON C.CROSSIC_ID = F.ID\n" +
                "                    WHERE A.ORDER_TYPE = 'retailReturn'\n" +
                "                       AND A.STATE IN('done','vouched')\n" +
                "                       AND A.SOURCE_BUSINESS_TYPE = 'RetailReturn'\n" +
                "                       AND C.CROSSIC_ID != NULL\n" +
                "                       AND A.ID = :BILL_ID";
//                "                       AND A.DATE_DONE >= to_date('2021-08-19','yyyy-mm-dd')  AND A.DATE_DONE < to_date('2021-09-01','yyyy-mm-dd')\n";

        return this.find(sql,param);
    }

    /**代勘提成 完成代勘*/
    public List<Map> queryDKTC_WCDK(JSONObject param){
        checkNull(param,"BILL_ID");

        //查询零售单
        String sql = "SELECT IR_TC_KDTC.NEXTVAL ID,SYSDATE CREATE_DATE, 'DK_TC_GD' TYPE, A.LAST_UPDATED_STAMP TJRQ, A.LAST_UPDATED_STAMP AS SYS_RQ,\n" +
                "                            A.ID RETAIL_ORDER_ID,A.DEPT_ID,A.CAR_LICENSE PLATE_LICENSE,A.SOURCE_FROM SOURCE,A.STORE_STAFF BILL_MAN,\n" +
                "               (SELECT D.COMPANY_ID FROM MAS_DEPT D WHERE D.ID = A.DEPT_ID) COMPANY_ID,\n" +
                "               (SELECT OPERATION FROM MAS_CORPORATE WHERE PARTY_ID = (SELECT P.PARTY_ID FROM MAS_PARTY_ROLE P WHERE 1 = 1 AND P.ID = (SELECT D.COMPANY_ID FROM MAS_DEPT D WHERE D.ID = A.DEPT_ID))) OPERATION\n" +
                "                        FROM CLAIM_WORK_ORDER A\n" +
                "                    WHERE A.STATE = 'finish'\n" +
                "                       AND A.ID = :BILL_ID";
//                "                       AND A.LAST_UPDATED_STAMP >= to_date('2021-08-19','yyyy-mm-dd')  AND A.LAST_UPDATED_STAMP < to_date('2021-09-01','yyyy-mm-dd')\n";
        return this.find(sql,param);
    }

    /**代勘提成 定损完成*/
    public List<Map> queryDKTC_DSWC(JSONObject param){
        checkNull(param,"BILL_ID");

        //查询零售单
        String sql = "SELECT IR_TC_KDTC.NEXTVAL ID,SYSDATE CREATE_DATE,'DK_TC_DS' TYPE, B.* from ( SELECT A.DATE_DONE TJRQ, A.DATE_DONE AS SYS_RQ,\n" +
                "                            A.ID RETAIL_ORDER_ID ,C.ID SOURCE,D.CAR_LICENSE PLATE_LICENSE,D.DEPT_ID,D.STORE_STAFF BILL_MAN,\n" +
                "                        (SELECT M.COMPANY_ID FROM MAS_DEPT M WHERE M.ID = D.DEPT_ID) COMPANY_ID,\n" +
                "                    (SELECT OPERATION FROM MAS_CORPORATE WHERE PARTY_ID = (SELECT P.PARTY_ID FROM MAS_PARTY_ROLE P WHERE 1 = 1 AND P.ID = (SELECT M.COMPANY_ID FROM MAS_DEPT M WHERE M.ID = A.DEPT_ID))) OPERATION,\n" +
                "                        ROW_NUMBER() OVER(ORDER BY D.LAST_UPDATED_STAMP DESC) NUM\n" +
                "               FROM RETAIL_ORDER A,SER_LOSS_QUOTATION B,SER_CAR_ACCIDENT C,CLAIM_WORK_ORDER D\n" +
                "                    WHERE A.SOURCE = B.ID AND B.CAR_ACCIDENT = C.ID AND C.ID = D.SOURCE_FROM\n" +
                "                       AND A.ID = :BILL_ID\n" +
//                "                       AND A.DATE_DONE >= to_date('2021-08-19','yyyy-mm-dd')  AND A.DATE_DONE < to_date('2021-09-01','yyyy-mm-dd')\n" +
                "                    )B WHERE B.NUM = 1";
        return this.find(sql,param);
    }

    /**代勘提成 定损完成 老系统*/
    public List<Map> queryDKTC_DSWC_OLD(JSONObject param){
        checkNull(param,"DATE_START","DATE_END");

        //查询老系统零售单定损
        String sql = "SELECT YYESB.IR_TC_KDTC.NEXTVAL ID,SYSDATE CREATE_DATE, 'DK_TC_DS_OLD' TYPE, A.JZRQ AS SYS_RQ, A.JZRQ AS TJRQ, A.XSDDH RETAIL_ORDER_ID,\n" +
                "                         B.CXBADH SOURCE, C.CAR_LICENSE PLATE_LICENSE, C.DEPT_ID,C.STORE_STAFF BILL_MAN,\n" +
                "                            (SELECT M.COMPANY_ID FROM MAS_DEPT M WHERE M.ID = C.DEPT_ID) COMPANY_ID,\n" +
                "                            (SELECT OPERATION FROM YYESB.MAS_CORPORATE WHERE PARTY_ID = (SELECT P.PARTY_ID FROM\n" +
                "                            YYESB.MAS_PARTY_ROLE P WHERE 1 = 1 AND P.ID = (SELECT M.COMPANY_ID FROM MAS_DEPT M WHERE M.ID = C.DEPT_ID))) OPERATION\n" +
                "                      FROM SAL_XSDD A, SER_DSBJD B\n" +
                "                      JOIN (SELECT T.*, ROW_NUMBER() OVER(PARTITION BY T.SOURCE_FROM ORDER BY T.LAST_UPDATED_STAMP DESC) NUM FROM CLAIM_WORK_ORDER T) C\n" +
                "                        ON B.CXBADH = C.SOURCE_FROM\n" +
                "                     WHERE A.DJZT = 10\n" +
                "                       AND A.LYDJLX = 'DS0'\n" +
                "                       AND A.LYDJHM = B.DSBJDH\n" +
                "                       AND C.NUM = 1\n" +
                "                       AND A.JZRQ >= :DATE_START AND A.JZRQ < :DATE_END";
        return this.find(sql,param);
    }

    /**定损销售提成 接案介绍费*/
    public List<Map> queryDSXSTC(JSONObject param){
        checkNull(param,"BILL_ID");

        //查询零售单
        String sql = "SELECT  IR_TC_DSXSTC.NEXTVAL ID,SYSDATE CREATE_DATE,'01' TYPE,C.CREATE_DATE TJRQ,\n" +
                "                           (case c.origin_business_type\n" +
                "                             when 'DS0' then\n" +
                "                              (SELECT t.dsyid FROM SER_DSPGD t WHERE t.cxbadh =\n" +
                "                              (SELECT t.cxbadh FROM SER_DSBJD t WHERE t.dsbjdh =  a.source) )\n" +
                "                             else\n" +
                "                              (select e.get_car_uid\n" +
                "                                 from SER_CAR_EVALUATION e\n" +
                "                                WHERE e.car_accident_id =\n" +
                "                                      (SELECT t.car_accident\n" +
                "                                         FROM SER_LOSS_QUOTATION t\n" +
                "                                        WHERE t.id = a.source))\n" +
                "                           end) GET_CAR_UID,\n" +
                "                         (SELECT T.CAR_LICENSE FROM FN_CUSTOMER_EXPAND T WHERE T.ID = A.CUSTOMER_EXPAND_ID) PLATE_LICENSE,\n" +
                "                       A.DEPT_ID,A.COMPANY_ID,\n" +
                "                       C.AMOUNT,\n" +
                "                       C.LOSS_QUOTATION_AMT,\n" +
                "                       C.SOURCE,\n" +
                "                       C.SOURCE_BUSINESS_TYPE\n" +
                "                  FROM FN_SETTLE_EXPENSE C, RETAIL_ORDER A\n" +
                "                 WHERE A.ID = C.SOURCE\n" +
                "                   AND C.TYPE = 'in'\n" +
                "                   AND C.EXPENSE_TYPE in ('infoExpense','innerInfoExpense')\n" +
                "                   AND C.ID = :BILL_ID";
//                "                   AND C.CREATE_DATE >= to_date('2021-08-19','yyyy-mm-dd') AND C.CREATE_DATE < to_date('2021-09-01','yyyy-mm-dd')\n";

        return this.find(sql,param);
    }

    /**定损销售提成 定损服务费*/
    public List<Map> queryDSFWTC(JSONObject param){
        checkNull(param,"BILL_ID");

        //查询零售单
        String sql = "SELECT IR_TC_DSXSTC.NEXTVAL ID, '02' TYPE,SYSDATE CREATE_DATE,'RetailOrder' SOURCE_BUSINESS_TYPE ,A.SERVICE_DEPT_ID DEPT_ID,(SELECT T.COMPANY_ID FROM MAS_DEPT T WHERE T.ID = A.SERVICE_DEPT_ID)COMPANY_ID,A.ID SOURCE,A.DATE_DONE TJRQ,A.DATE_DONE AS SYS_RQ," +
                "                            (SELECT T.CAR_LICENSE FROM FN_CUSTOMER_EXPAND T WHERE T.ID = A.CUSTOMER_EXPAND_ID) PLATE_LICENSE, " +
                "                   (SELECT T.LOSS_OFFICER FROM SER_LOSS_QUOTATION T WHERE T.ID = A.SOURCE) GET_CAR_UID, " +
                "                            (B.AMT_TOTAL - " +
                "                            (SELECT SUM(T.PRODUCT_QTY * T.TAX_COST_PRICE) FROM ACC_VOUCHER_ITEM T WHERE T.VOUCHER_ID = B.ID) " +
                "                            - B.LOSS_QUOTATION_FEE_AMT) AMOUNT " +
                "                        FROM  RETAIL_ORDER A JOIN (SELECT T.ID,T.AMT_TOTAL,T.LOSS_QUOTATION_FEE_AMT,T.ORIGIN FROM ACC_VOUCHER T WHERE T.CLASSIFICATION = '09') B ON B.ORIGIN = A.ID " +
                "           WHERE A.SCENE_ID = 'RO003' " +
                "                 AND A.ID = :BILL_ID ";
//                "                 AND A.DATE_DONE >= to_date('2021-08-19','yyyy-mm-dd') AND A.DATE_DONE < to_date('2021-09-01','yyyy-mm-dd')\n";
        return this.find(sql,param);
    }


    /**定损销售提成 车险提成规则*/
    public List<Map> queryCXTC(JSONObject param){
        checkNull(param,"BILL_ID");

        //查询零售单
        String sql = "SELECT A.CREATE_DATE TJRQ, " +
                "                           A.COMPANY_ID, " +
                "                           A.DEPT_ID, " +
                "                           A.ID, " +
                "                           A.PLATE_LICENSE, " +
                "                           A.INSURANCE_TYPE, " +
                "                           SYSDATE CREATE_DATE, " +
                "                           A.NET_PREMIUM, " +
                "                           A.SALESMAN_ID, " +
                "                           A.TOTAL_PERCENT FN_PROFIT_RATIO, " +
                "                           A.COMMISSION_TOTAL_AMOUNT FN_PROFIT, " +
                "                           B.FN_COMMISSION, " +
                "                           (SELECT sum(commercial_Insurance + compulsory_Insurance) FROM insurance_order t WHERE T.APP_ORDER = A.APP_ORDER)CXJYE, " +
                "                           A.SUPPLIER_ID, " +
                "                           A.CITY, " +
                "                           A.PROVINCE, " +
                "                           A.USE, " +
                "                           A.CAR_OWNERSHIP, " +
                "                           GET_DATE(A.START_DATE) START_DATE, " +
                "                           GET_DATE(A.END_DATE) END_DATE, " +
                "                           A.ITEM_THIRD_PRICE " +
                "                      FROM POLICY_INFORMATION A, POLICY_INFORMATION_ITEM B " +
                "                     WHERE A.ID = B.POLICY_INFORMATION_ID " +
                "                       AND A.INSURANCE_TYPE in ('0','2') " +
                "                       AND B.commission_type != 'PolicyInformationItem.commissionType.B' " +
                "                       AND NOT Exists (SELECT 1 FROM tc_cxtc t WHERE t.id =  a.id) " +
                "                       AND A.SOURCE_BUSINESS_TYPE = 'AppOrder' " +
                "                       AND A.ID =  :BILL_ID";
//                "                 AND A.RECEIPT_DATE >= to_date('2021-08-19','yyyy-mm-dd') AND A.RECEIPT_DATE < to_date('2021-09-01','yyyy-mm-dd')\n";

        return this.find(sql,param);
    }


    /**定损销售提成 定损跟踪提成_老系统*/
    public List<Map> queryOldDSGZTC(JSONObject param){
        checkNull(param,"BILL_ID");

        //查询零售单
        String sql = "SELECT C.DATE_APPROVE TJRQ,SYSDATE CREATE_DATE,C.DATE_APPROVE AS SYS_RQ," +
                "                         A.DEPT_ID,A.ID CAR_ACCIDENT_ID,B.DSBJDH LOSS_QUOTATION_ID,C.COMPANY_ID," +
                "                        (SELECT T.CAR_LICENSE FROM FN_CUSTOMER_EXPAND T WHERE T.ID = A.CUSTOMER_EXPAND_ID AND ROWNUM=1) PLATE_LICENSE," +
                "                        C.ID RETAIL_ORDER_ID,A.CASE_TYPE ,A.INSURANCE_COMPANY_ID,A.STAFF_ID,10 TCJE" +
                "                    FROM SER_CAR_ACCIDENT   A," +
                "                         SER_DSBJD    B," +
                "                         RETAIL_ORDER       C" +
                "                   WHERE A.ID = B.CXBADH" +
                "                     AND B.DSBJDH = C.SOURCE" +
                "                     AND A.CASE_TYPE IN('001492','001490','001494')" +
                "                     AND A.STAFF_ID IS NOT NULL" +
                "                     AND C.SOURCE_BUSINESS_TYPE = 'DS0'" +
                "                     AND C.STATE IN( 'done','vouched' )" +
                "                     AND C.ID = :BILL_ID";
//                "                     AND C.DATE_APPROVE >= to_date('2022-03-01','yyyy-mm-dd') AND C.DATE_APPROVE < to_date('2022-03-23','yyyy-mm-dd')";
        return this.find(sql,param);
    }

    /**定损销售提成 定损跟踪提成_新系统*/
    public List<Map> queryNewDSGZTC(JSONObject param){
        checkNull(param,"BILL_ID");

        //查询零售单
        String sql = "SELECT C.DATE_APPROVE TJRQ,SYSDATE CREATE_DATE,C.DATE_APPROVE AS SYS_RQ, " +
                "                             A.DEPT_ID,A.ID CAR_ACCIDENT_ID,B.ID LOSS_QUOTATION_ID,C.COMPANY_ID," +
                "                            (SELECT T.CAR_LICENSE FROM FN_CUSTOMER_EXPAND T WHERE T.ID = A.CUSTOMER_EXPAND_ID AND ROWNUM=1) PLATE_LICENSE," +
                "                            C.ID RETAIL_ORDER_ID,A.CASE_TYPE ,A.INSURANCE_COMPANY_ID,A.STAFF_ID,10 TCJE" +
                "                        FROM SER_CAR_ACCIDENT   A," +
                "                             SER_LOSS_QUOTATION B," +
                "                             RETAIL_ORDER       C" +
                "                       WHERE A.ID = B.CAR_ACCIDENT" +
                "                         AND B.ID = C.SOURCE" +
                "                         AND A.CASE_TYPE IN('001492','001490','001494')" +
                "                         AND A.STAFF_ID IS NOT NULL" +
                "                         AND C.SOURCE_BUSINESS_TYPE = 'LossQuotation'" +
                "                         AND C.STATE IN( 'done','vouched' )" +
                "                         AND C.ID = :BILL_ID";
//                "                         AND C.DATE_APPROVE >= to_date('2022-03-01','yyyy-mm-dd') AND C.DATE_APPROVE < to_date('2022-03-23','yyyy-mm-dd')";
        return this.find(sql,param);
    }

    /** 单据施工提成 零售正单*/
    public List<Map> queryDJSGTC_ZD(JSONObject param){
        checkNull(param,"BILL_ID");

        String sql = "SELECT IR_TC_DJSGTC.NEXTVAL ID,SYSDATE CREATE_DATE, A.DATE_DONE AS SYS_RQ, A.DATE_DONE TJRQ, A.ID RETAIL_ORDER_ID,B.ID RETAIL_ITEM_ID,\n" +
                "      B.PRODUCT_ID,(CASE WHEN B.ASSESSMENT_COST_PRICE = 0 THEN  (SELECT SUM(NVL(Z.TAX_COST_PRICE * Z.PRODUCT_QTY, 0)) \n" +
                "                        FROM ACC_VOUCHER_ITEM Z,ACC_VOUCHER X\n" +
                "                        WHERE A.ID IN(X.ORIGIN,X.SOURCE) AND Z.VOUCHER_ID = X.ID AND Z.PRODUCT_ID = B.PRODUCT_ID AND X.VOUCHER_TYPE = 'CUSTOMER_SALE') ELSE \n" +
                "                        B.ASSESSMENT_COST_PRICE END)  ASSESSMENT_COST_PRICE, B.PRICE_TOTAL SALE_AMT, B.PRODUCT_UOM_QTY SALE_QTY, B.PRODUCT_UOM_ID,\n" +
                "       (SELECT B.SERVE_TYPE_ID FROM MAS_SERVE_CATEGORY B, MAS_PRODUCT C\n" +
                "                              WHERE C.CLASSIFY_ID = B.CLASSIFY_ID AND C.ID =  B.PRODUCT_ID and rownum =1) SERVE_TYPE_ID,\n" +
                "                            (SELECT T.STORE_TYPE_NAME FROM MAS_STORE T WHERE T.DEPT_ID = D.BELONG_DEPT_ID ) STORE_TYPE_NAME,D.ID ORDER_ID,\n" +
                "       D.BELONG_DEPT_ID DEPT_ID, C.SERVICE_PLAN_ID SERVICE_PLAN, D.ID SERVICE_WORK_ORDER_ID, D.ID MAINTENANCE_ORDER_ID,\n" +
                "       (SELECT T.CAR_LICENSE FROM FN_CUSTOMER_EXPAND T WHERE T.ID = D.FN_CUSTOMER_EXPAND_ID) PLATE_LICENSE,\n" +
                "       (SELECT T.CLASSIFY_ID FROM MAS_PRODUCT T WHERE T.ID =  B.PRODUCT_ID) CLASSIFY_ID, D.COMPANY_ID,\n" +
                "       (SELECT T.BRAND_ID FROM MAS_PRODUCT T WHERE T.ID =  B.PRODUCT_ID) BRAND_ID,\n" +
                "       (SELECT SUBSTR(T.CLASSIFY_ID,1,1) FROM MAS_PRODUCT T WHERE T.ID =  B.PRODUCT_ID)SUB_CLASSIFY,(SELECT SUM(O.AMOUNT)\n" +
                "        FROM  RETAIL_ORDER_PAYMENT_METHOD O\n" +
                "        WHERE O.RETAIL_ORDER_ID = A.ID\n" +
                "        AND O.PAYMENT_METHOD_TYPE = 10) PAYMENT_METHOD,\n" +
                "                            (SELECT SUBSTR(T.CLASSIFY_ID,1,5) FROM MAS_PRODUCT T WHERE T.ID =  B.PRODUCT_ID)SUBSTR_CLASSIFY,\n" +
                "                            (SELECT to_number(nvl(T.SPECIFICATIONS,0)) FROM MAS_PRODUCT T WHERE T.ID = B.PRODUCT_ID  and t.CLASSIFY_ID like 'S0106%') SPECIFICATIONS,\n" +
                "                           TO_CHAR(D.WORKER1_SHOW || '|' || D.WORKER2_SHOW || '|' || D.WORKER3_SHOW || '|' || D.WORKER4_SHOW) THE_WORKER,\n" +
                "                           TO_CHAR(D.WORKER1) WORKER1, TO_CHAR(D.WORKER2) WORKER2,\n" +
                "                           TO_CHAR(D.WORKER3) WORKER3, TO_CHAR(D.WORKER4) WORKER4\n" +
                "           FROM RETAIL_ORDER A, RETAIL_ORDER_ITEM B, SER_SERVICE_PLAN_ITEM C, SER_MAINTENANCE_ORDER D , MAS_PRODUCT E\n" +
                "       WHERE A.ID = B.SALE_ID AND B.ORIGINNUMBER = C.ID  AND (C.ID = D.SERVICE_PLAN_ITEM OR C.RELEVANCE_WORK_ORDER = D.ID )\n" +
                "                       AND C.ASSIST_TYPE = 'ServicePlanItem.assistType.normal'\n" +
                "                       AND D.STATE = 'finish'\n" +
                "                       AND (A.STATE = 'done' or A.STATE = 'vouched')\n" +
                "                       AND (A.CLAIM_TYPE IS NULL OR A.SCENE_ID = 'RO003' )\n" +
                //"                       AND A.PACKAGE_COMMISSION = 0\n" +
                "                       AND A.APP_CALC_FLAG<>1 \n " +
                "                       AND (A.SOURCE_BUSINESS_TYPE is null or A.SOURCE_BUSINESS_TYPE != 'RetailReturn' )\n" +
                "                       AND B.PRODUCT_ID = E.ID AND E.WORKER_VALID = 1\n" +
                "                       AND A.ID = :BILL_ID\n" +
                "                       -- and B.PRICE_TOTAL < 0\n" +
                "                       -- AND A.CLAIM_TYPE IS NULL\n";
//                "                       AND A.DATE_DONE >= to_date('2022-01-01','yyyy-mm-dd') AND A.DATE_DONE < to_date('2022-05-31','yyyy-mm-dd')\n";
        return this.find(sql,param);
    }

    /** 单据施工提成 整单内协和工单内协*/
    public List<Map> queryDJSGTC_NX(JSONObject param){
        checkNull(param,"BILL_ID");

        String sql = "SELECT IR_TC_DJSGTC.NEXTVAL ID,SYSDATE CREATE_DATE, A.RECEIVABLE_DATE AS SYS_RQ, A.RECEIVABLE_DATE TJRQ, A.ID RETAIL_ORDER_ID,\n" +
                "       B.PRODUCT_ID, B.SALES_AMOUNT SALE_AMT, B.SALE_QTY SALE_QTY, B.PRODUCT_UOM_ID, B.SERVE_TYPE_ID,\n" +
                "       (SELECT T.STORE_TYPE_NAME FROM MAS_STORE T WHERE T.DEPT_ID = D.BELONG_DEPT_ID) STORE_TYPE_NAME, D.ID ORDER_ID,\n" +
                "       D.BELONG_DEPT_ID DEPT_ID, B.SERVICE_PLAN_ID SERVICE_PLAN, D.ID SERVICE_WORK_ORDER_ID, D.ID MAINTENANCE_ORDER_ID,\n" +
                "       (SELECT T.CAR_LICENSE FROM FN_CUSTOMER_EXPAND T WHERE T.ID = D.FN_CUSTOMER_EXPAND_ID) PLATE_LICENSE, (SELECT SUM(O.AMOUNT)\n" +
                "        FROM  RETAIL_ORDER_PAYMENT_METHOD O,RETAIL_ORDER P\n" +
                "        WHERE P.ORIGIN = A.ID\n" +
                "        AND O.RETAIL_ORDER_ID = P.ID\n" +
                "        AND O.PAYMENT_METHOD_TYPE = 10) PAYMENT_METHOD,\n" +
                "       (CASE WHEN (SELECT Z.ASSESSMENT_COST_PRICE FROM RETAIL_ORDER_ITEM Z,RETAIL_ORDER V WHERE Z.PRODUCT_ID = C.ID  AND Z.SALE_ID = V.ID AND V.ORIGIN = A.ID ) =0 THEN  (SELECT SUM(NVL(Z.TAX_COST_PRICE * Z.PRODUCT_QTY, 0)) \n" +
                "                        FROM ACC_VOUCHER_ITEM Z,ACC_VOUCHER X,RETAIL_ORDER G,RETAIL_ORDER_ITEM V\n" +
                "                        WHERE G.ORIGIN = A.ID AND  V.SALE_ID = G.ID AND V.COMPANY_ID = X.COMPANY_ID AND G.ID IN(X.ORIGIN,X.SOURCE) AND Z.VOUCHER_ID = X.ID AND Z.PRODUCT_ID = C.ID AND X.VOUCHER_TYPE = 'CUSTOMER_SALE') ELSE \n" +
                "                        (SELECT Z.ASSESSMENT_COST_PRICE FROM RETAIL_ORDER_ITEM Z,RETAIL_ORDER V WHERE Z.PRODUCT_ID = C.ID  AND Z.SALE_ID = V.ID AND V.ORIGIN = A.ID ) END)  ASSESSMENT_COST_PRICE,C.CLASSIFY_ID CLASSIFY_ID, C.BRAND_ID BRAND_ID, D.COMPANY_ID,\n" +
                "       (SELECT SUBSTR(T.CLASSIFY_ID, 1, 1) FROM MAS_PRODUCT T WHERE T.ID = B.PRODUCT_ID) SUB_CLASSIFY,\n" +
                "       (SELECT SUBSTR(T.CLASSIFY_ID, 1, 5) FROM MAS_PRODUCT T WHERE T.ID = B.PRODUCT_ID) SUBSTR_CLASSIFY,\n" +
                "       (SELECT TO_NUMBER(NVL(T.SPECIFICATIONS, 0))\n" +
                "           FROM MAS_PRODUCT T\n" +
                "          WHERE T.ID = B.PRODUCT_ID\n" +
                "            AND T.CLASSIFY_ID LIKE 'S0106%') SPECIFICATIONS, TO_CHAR(D.WORKER1) WORKER1, TO_CHAR(D.WORKER2) WORKER2,\n" +
                "       TO_CHAR(D.WORKER3) WORKER3, TO_CHAR(D.WORKER4) WORKER4\n" +
                "  FROM SER_SERVICE_PLAN A, SER_SERVICE_PLAN_ITEM B, MAS_PRODUCT C, SER_MAINTENANCE_ORDER D\n" +
                " WHERE A.ID = B.SERVICE_PLAN_ID\n" +
                "   AND B.PRODUCT_ID = C.ID\n" +
                "   AND B.ID = D.SERVICE_PLAN_ITEM\n" +
                " AND (B.ID = D.SERVICE_PLAN_ITEM OR B.RELEVANCE_WORK_ORDER = D.ID )\n" +
                "   AND A.STATE = 'receivables'\n" +
                "   AND A.PLAN_TYPE IN ('allAssist', 'someAssist')\n" +
                "   AND (A.SOURCE_BUSINESS_TYPE is null or A.SOURCE_BUSINESS_TYPe <> 'LossQuotation')\n" +
                "   AND A.RECEIVABLE_DATE IS NOT NULL\n" +
                "   AND (SELECT COMPANY_ID FROM MAS_DEPT WHERE ID = A.SALE_DEPT_ID) = (SELECT COMPANY_ID FROM MAS_DEPT WHERE ID = A.SERVICE_DEPT_ID)" +
                "   AND A.ID = :BILL_ID";
//                "   AND A.DATE_DONE >= to_date('2022-01-01','yyyy-mm-dd') AND A.DATE_DONE < to_date('2022-05-31','yyyy-mm-dd')\n";
        return this.find(sql,param);
    }

    /** 单据施工提成 零售负单*/
    public List<Map> queryDJSGTC_FD(JSONObject param){
        checkNull(param,"BILL_ID");

        String sql = "SELECT IR_TC_DJSGTC.NEXTVAL ID,\n" +
                "                           A.DATE_DONE AS SYS_RQ,\n" +
                "                           SYSDATE CREATE_DATE,\n" +
                "                           A.LAST_UPDATED_STAMP TJRQ,\n" +
                "                           (CASE WHEN B.ASSESSMENT_COST_PRICE =0 THEN  (SELECT SUM(NVL(Z.TAX_COST_PRICE * Z.PRODUCT_QTY, 0)) \n" +
                "                        FROM ACC_VOUCHER_ITEM Z,ACC_VOUCHER X\n" +
                "                        WHERE A.ID IN(X.ORIGIN,X.SOURCE) AND Z.VOUCHER_ID = X.ID AND Z.PRODUCT_ID = B.PRODUCT_ID AND X.VOUCHER_TYPE = 'CUSTOMER_SALE') ELSE \n" +
                "                        B.ASSESSMENT_COST_PRICE END) ASSESSMENT_COST_PRICE,(SELECT SUM(O.AMOUNT)\n" +
                "        FROM  RETAIL_ORDER_PAYMENT_METHOD O\n" +
                "        WHERE O.RETAIL_ORDER_ID = A.ID\n" +
                "        AND O.PAYMENT_METHOD_TYPE = 10) PAYMENT_METHOD,\n" +
                "                           A.ID RETAIL_ORDER_ID,\n" +
                "                           B.ID RETAIL_ITEM_ID,\n" +
                "                           B.PRODUCT_ID,\n" +
                "                           B.PRICE_TOTAL SALE_AMT,\n" +
                "                           B.PRODUCT_UOM_QTY SALE_QTY,\n" +
                "                           B.PRODUCT_UOM_ID,\n" +
                "                           (SELECT B.SERVE_TYPE_ID\n" +
                "                              FROM MAS_SERVE_CATEGORY B, MAS_PRODUCT C\n" +
                "                             WHERE C.CLASSIFY_ID = B.CLASSIFY_ID\n" +
                "                               AND C.ID = B.PRODUCT_ID) SERVE_TYPE_ID,\n" +
                "                           (SELECT T.STORE_TYPE_NAME\n" +
                "                              FROM MAS_STORE T\n" +
                "                             WHERE T.DEPT_ID = D.BELONG_DEPT_ID) STORE_TYPE_NAME,\n" +
                "                           D.ID ORDER_ID,\n" +
                "                           D.BELONG_DEPT_ID DEPT_ID,\n" +
                "                           C.SERVICE_PLAN_ID SERVICE_PLAN,\n" +
                "                           D.ID SERVICE_WORK_ORDER_ID,\n" +
                "                           D.ID MAINTENANCE_ORDER_ID,\n" +
                "                           (SELECT T.CAR_LICENSE\n" +
                "                              FROM FN_CUSTOMER_EXPAND T\n" +
                "                             WHERE T.ID = D.FN_CUSTOMER_EXPAND_ID) PLATE_LICENSE,\n" +
                "                           (SELECT T.CLASSIFY_ID FROM MAS_PRODUCT T WHERE T.ID = B.PRODUCT_ID and rownum =1) CLASSIFY_ID,\n" +
                "                           (SELECT T.BRAND_ID FROM MAS_PRODUCT T WHERE T.ID = B.PRODUCT_ID and rownum =1) BRAND_ID,\n" +
                "                           D.COMPANY_ID,\n" +
                "                           (SELECT SUBSTR(T.CLASSIFY_ID, 1, 1)\n" +
                "                              FROM MAS_PRODUCT T\n" +
                "                             WHERE T.ID = B.PRODUCT_ID) SUB_CLASSIFY,\n" +
                "                           (SELECT SUBSTR(T.CLASSIFY_ID, 1, 5)\n" +
                "                              FROM MAS_PRODUCT T\n" +
                "                             WHERE T.ID = B.PRODUCT_ID) SUBSTR_CLASSIFY,\n" +
                "                           (SELECT to_number(nvl(T.SPECIFICATIONS, 0))\n" +
                "                              FROM MAS_PRODUCT T\n" +
                "                             WHERE T.ID = B.PRODUCT_ID\n" +
                "                               and t.CLASSIFY_ID like 'S0106%') SPECIFICATIONS,\n" +
                "                           TO_CHAR(D.WORKER1_SHOW || '|' || D.WORKER2_SHOW || '|' || D.WORKER3_SHOW || '|' || D.WORKER4_SHOW) THE_WORKER,\n" +
                "                           TO_CHAR(D.WORKER1) WORKER1,\n" +
                "                           TO_CHAR(D.WORKER2) WORKER2,\n" +
                "                           TO_CHAR(D.WORKER3) WORKER3,\n" +
                "                           TO_CHAR(D.WORKER4) WORKER4\n" +
                "                      FROM RETAIL_ORDER A,\n" +
                "                           (SELECT (SELECT t.originnumber\n" +
                "                                      FROM RETAIL_ORDER_ITEM t\n" +
                "                                     WHERE id = a.RETAIL_ORDER_ITEM_ID) ORIGINNUMBER,\n" +
                "                                   t.assessment_cost_price,t.product_id,\n" +
                "                                   t.price_total,\n" +
                "                                   t.product_uom_qty,\n" +
                "                                   t.product_uom_id,\n" +
                "                                   t.sale_id,\n" +
                "                                   t.id\n" +
                "                              FROM RETAIL_ORDER_ITEM t, retail_return_item a\n" +
                "                             WHERE a.id = t.originnumber) B,\n" +
                "                           SER_SERVICE_PLAN_ITEM C,\n" +
                "                           SER_MAINTENANCE_ORDER D,\n" +
                "                           MAS_PRODUCT E" +
                "                     WHERE A.ID = B.SALE_ID\n" +
                "                       AND B.ORIGINNUMBER = C.ID\n" +
                "                       AND (C.ID = D.SERVICE_PLAN_ITEM OR C.RELEVANCE_WORK_ORDER = D.ID)\n" +
                "                       AND C.ASSIST_TYPE = 'ServicePlanItem.assistType.normal'\n" +
                "                       AND D.STATE = 'finish'\n" +
                //"                       AND A.PACKAGE_COMMISSION = 0\n" +
                "                   AND A.APP_CALC_FLAG<>1 \n " +
                "                       AND A.SOURCE_BUSINESS_TYPE = 'RetailReturn'\n" +
                "                       AND B.PRODUCT_ID = E.ID AND E.WORKER_VALID = 1\n" +
                "                       AND A.ID = :BILL_ID\n";
//                "                       AND A.DATE_DONE >= to_date('2022-01-01','yyyy-mm-dd') AND A.DATE_DONE < to_date('2022-05-31','yyyy-mm-dd')\n";

        return this.find(sql,param);
    }


    /** 单据施工提成 整单内协和工单内协正单*/
    public List<Map> queryDJSGTC_NXZD(JSONObject param){
        checkNull(param,"BILL_ID");

        String sql = "SELECT IR_TC_DJSGTC.NEXTVAL ID,SYSDATE CREATE_DATE, A.RECEIVABLE_DATE AS SYS_RQ, A.RECEIVABLE_DATE TJRQ, A.ID RETAIL_ORDER_ID,\n" +
                "       B.PRODUCT_ID, B.ID RETAIL_ITEM_ID,B.SALES_AMOUNT SALE_AMT, B.SALE_QTY SALE_QTY, B.PRODUCT_UOM_ID, B.SERVE_TYPE_ID,\n" +
                "       (SELECT T.STORE_TYPE_NAME FROM MAS_STORE T WHERE T.DEPT_ID = D.BELONG_DEPT_ID) STORE_TYPE_NAME, D.ID ORDER_ID,\n" +
                "       D.BELONG_DEPT_ID DEPT_ID, B.SERVICE_PLAN_ID SERVICE_PLAN, D.ID SERVICE_WORK_ORDER_ID, D.ID MAINTENANCE_ORDER_ID,\n" +
                "       (SELECT T.CAR_LICENSE FROM FN_CUSTOMER_EXPAND T WHERE T.ID = D.FN_CUSTOMER_EXPAND_ID) PLATE_LICENSE,\n" +
                "       C.CLASSIFY_ID CLASSIFY_ID, C.BRAND_ID BRAND_ID, D.COMPANY_ID, \n" +
                "       (SELECT SUBSTR(T.CLASSIFY_ID, 1, 1) FROM MAS_PRODUCT T WHERE T.ID = B.PRODUCT_ID) SUB_CLASSIFY,\n" +
                "       (SELECT SUBSTR(T.CLASSIFY_ID, 1, 5) FROM MAS_PRODUCT T WHERE T.ID = B.PRODUCT_ID) SUBSTR_CLASSIFY,\n" +
                "       (SELECT TO_NUMBER(NVL(T.SPECIFICATIONS, 0))\n" +
                "           FROM MAS_PRODUCT T\n" +
                "          WHERE T.ID = B.PRODUCT_ID\n" +
                "            AND T.CLASSIFY_ID LIKE 'S0106%') SPECIFICATIONS, TO_CHAR(D.WORKER1) WORKER1, TO_CHAR(D.WORKER2) WORKER2,\n" +
                "       TO_CHAR(D.WORKER1_SHOW || '|' || D.WORKER2_SHOW || '|' || D.WORKER3_SHOW || '|' || D.WORKER4_SHOW) THE_WORKER,\n" +
                "       TO_CHAR(D.WORKER3) WORKER3, TO_CHAR(D.WORKER4) WORKER4\n" +
                "  FROM SER_SERVICE_PLAN A, SER_SERVICE_PLAN_ITEM B, MAS_PRODUCT C, SER_MAINTENANCE_ORDER D\n" +
                " WHERE A.ID = B.SERVICE_PLAN_ID\n" +
                "   AND B.PRODUCT_ID = C.ID\n" +
                "   AND B.ID = D.SERVICE_PLAN_ITEM\n" +
                " AND (B.ID = D.SERVICE_PLAN_ITEM OR B.RELEVANCE_WORK_ORDER = D.ID )\n" +
                "   AND A.STATE = 'receivables'\n" +
                "   AND A.PLAN_TYPE IN ('allAssist', 'someAssist')\n" +
                "   AND (A.SOURCE_BUSINESS_TYPE is null or A.SOURCE_BUSINESS_TYPe <> 'LossQuotation')\n" +
                "   AND A.RECEIVABLE_DATE IS NOT NULL\n" +
                "   AND (SELECT COMPANY_ID FROM MAS_DEPT WHERE ID = A.SALE_DEPT_ID) = (SELECT COMPANY_ID FROM MAS_DEPT WHERE ID = A.SERVICE_DEPT_ID)" +
                "   AND A.ID = :BILL_ID";
        return this.find(sql,param);
    }

    /** 单据施工提成 整单内协和工单内协负单*/
    public List<Map> queryDJSGTC_NXFD(JSONObject param){
        checkNull(param,"BILL_ID");
        String sql = "SELECT IR_TC_DJSGTC.NEXTVAL ID,\n" +
                "                           A.RECEIVABLE_DATE AS SYS_RQ,\n" +
                "                           SYSDATE CREATE_DATE,\n" +
                "                                   (SELECT t.TCJE1\n" +
                "                           FROM tc_djsgtc t\n" +
                "                           where t.service_plan = y.service_plan_id\n" +
                "                           and t.PRODUCT_ID = y.product_id" +
                "                           and t.sale_qty > 0) OLDTCJE1,\n" +
                "                                   (SELECT t.TCJE2\n" +
                "                           FROM tc_djsgtc t\n" +
                "                           where t.service_plan = y.service_plan_id\n" +
                "                           and t.PRODUCT_ID = y.product_id" +
                "                           and t.sale_qty > 0) OLDTCJE2,\n" +
                "                                   (SELECT t.TCJE3\n" +
                "                           FROM tc_djsgtc t\n" +
                "                           where t.service_plan = y.service_plan_id\n" +
                "                           and t.PRODUCT_ID = y.product_id" +
                "                           and t.sale_qty > 0) OLDTCJE3,\n" +
                "                                   (SELECT t.TCJE4\n" +
                "                           FROM tc_djsgtc t\n" +
                "                           where t.service_plan = y.service_plan_id\n" +
                "                           and t.PRODUCT_ID = y.product_id" +
                "                           and t.sale_qty > 0) OLDTCJE4,\n" +
                "                                   (SELECT t.SALE_QTY\n" +
                "                           FROM tc_djsgtc t\n" +
                "                           where t.service_plan = y.service_plan_id\n" +
                "                           and t.PRODUCT_ID = y.product_id" +
                "                           and t.sale_qty > 0) OLDSALE_QTY,\n" +
                "                                   (SELECT i.product_uom_qty\n" +
                "                           FROM retail_order_item i\n" +
                "                           where i.sale_id = x.id\n" +
                "                           and i.product_id = c.id) SALE_QTY,\n" +
                "                                   y.primary_price_unit * (SELECT i.product_uom_qty\n" +
                "                           FROM retail_order_item i\n" +
                "                           where i.sale_id = x.id\n" +
                "                           and i.product_id = c.id) SALE_AMT,\n" +
                "                                   x.ID RETAIL_ORDER_ID,\n" +
                "                                   B.ID RETAIL_ITEM_ID,\n" +
                "                                   A.RECEIVABLE_DATE TJRQ,\n" +
                "                                   y.PRODUCT_ID,\n" +
                "                                   y.PRODUCT_UOM_ID,\n" +
                "                                   y.SERVE_TYPE_ID,\n" +
                "                                   (SELECT T.STORE_TYPE_NAME\n" +
                "                           FROM MAS_STORE T\n" +
                "                           WHERE T.DEPT_ID = D.BELONG_DEPT_ID) STORE_TYPE_NAME,\n" +
                "                                   D.ID ORDER_ID,\n" +
                "                                   D.BELONG_DEPT_ID DEPT_ID,\n" +
                "                                   y.SERVICE_PLAN_ID SERVICE_PLAN,\n" +
                "                                   D.ID SERVICE_WORK_ORDER_ID,\n" +
                "                                   D.ID MAINTENANCE_ORDER_ID,\n" +
                "                                   (SELECT T.CAR_LICENSE\n" +
                "                           FROM FN_CUSTOMER_EXPAND T\n" +
                "                           WHERE T.ID = D.FN_CUSTOMER_EXPAND_ID) PLATE_LICENSE,\n" +
                "                                   C.CLASSIFY_ID CLASSIFY_ID, C.BRAND_ID BRAND_ID, \n" +
                "                                   D.COMPANY_ID,\n" +
                "                                   (SELECT SUBSTR(T.CLASSIFY_ID, 1, 1)\n" +
                "                           FROM MAS_PRODUCT T\n" +
                "                           WHERE T.ID = y.PRODUCT_ID) SUB_CLASSIFY,\n" +
                "                                   (SELECT SUBSTR(T.CLASSIFY_ID, 1, 5)\n" +
                "                           FROM MAS_PRODUCT T\n" +
                "                           WHERE T.ID = y.PRODUCT_ID) SUBSTR_CLASSIFY,\n" +
                "                                   (SELECT TO_NUMBER(NVL(T.SPECIFICATIONS, 0))\n" +
                "                           FROM MAS_PRODUCT T\n" +
                "                           WHERE T.ID = y.PRODUCT_ID\n" +
                "                           AND T.CLASSIFY_ID LIKE 'S0106%') SPECIFICATIONS,\n" +
                "                                   TO_CHAR(D.WORKER1_SHOW || '|' || D.WORKER2_SHOW || '|' || D.WORKER3_SHOW || '|' || D.WORKER4_SHOW) THE_WORKER,\n" +
                "                                   TO_CHAR(D.WORKER1) WORKER1,\n" +
                "                                   TO_CHAR(D.WORKER2) WORKER2,\n" +
                "                                   TO_CHAR(D.WORKER3) WORKER3,\n" +
                "                                   TO_CHAR(D.WORKER4) WORKER4\n" +
                "                           FROM SER_SERVICE_PLAN      A,\n" +
                "                                   SER_SERVICE_PLAN_ITEM B,\n" +
                "                                   MAS_PRODUCT           C,\n" +
                "                                   SER_MAINTENANCE_ORDER D,\n" +
                "                                   retail_order          z,\n" +
                "                                   ser_service_plan_item y,\n" +
                "                                   retail_return         x\n" +
                "                           WHERE x.sale_id = z.id\n" +
                "                           and z.origin = a.id\n" +
                "                           and b.service_plan_id = a.id\n" +
                "                           and y.PARENT_ITEM_ID = b.id\n" +
                "                           AND y.PRODUCT_ID = C.ID\n" +
                "                           AND y.ID = D.SERVICE_PLAN_ITEM\n" +
                "                           AND (y.ID = D.SERVICE_PLAN_ITEM OR y.RELEVANCE_WORK_ORDER = D.ID)\n" +
                "                           AND A.STATE = 'receivables'\n" +
                "                           AND (A.SOURCE_BUSINESS_TYPE is null or\n" +
                "                           A.SOURCE_BUSINESS_TYPe <> 'LossQuotation')\n" +
                "                           AND A.RECEIVABLE_DATE IS NOT NULL\n" +
                "                           AND (SELECT COMPANY_ID FROM MAS_DEPT WHERE ID = A.SALE_DEPT_ID) =\n" +
                "                                   (SELECT COMPANY_ID FROM MAS_DEPT WHERE ID = A.SERVICE_DEPT_ID)\n" +
                "                           AND (SELECT t.plan_type\n" +
                "                                   FROM ser_service_plan t\n" +
                "                                   where t.id = y.service_plan_id) IN ('allAssist', 'someAssist')\n" +
                "                           AND x.state = 'done'\n" +
                "                           AND (SELECT i.product_uom_qty\n" +
                "                               FROM retail_order_item i\n" +
                "                               where i.sale_id = x.id\n" +
                "                               and i.product_id = c.id) IS NOT NULL\n" +
                "                           AND x.id = :BILL_ID";
        return this.find(sql,param);
    }



    /** 分销单据施工提成 非定损*/
    public List<Map> queryFXDJSGTC_FDS(JSONObject param){
        checkNull(param,"BILL_ID");

        String sql = "SELECT IR_TC_DJSGTC.NEXTVAL ID,SYSDATE CREATE_DATE, A.DATE_DONE AS SYS_RQ, A.DATE_DONE TJRQ, A.ID RETAIL_ORDER_ID,\n" +
                "                         B.ID RETAIL_ITEM_ID,B.PRODUCT_ID, B.PRICE_TOTAL SALE_AMT, B.PRODUCT_UOM_QTY SALE_QTY, B.PRODUCT_UOM_ID, D.BUSINESS_TYPE,\n" +
                "                          (SELECT B.SERVE_TYPE_ID FROM MAS_SERVE_CATEGORY B, MAS_PRODUCT C\n" +
                "                                      WHERE C.CLASSIFY_ID = B.CLASSIFY_ID AND C.ID =  B.PRODUCT_ID and rownum =1) SERVE_TYPE_ID,\n" +
                "                         (SELECT to_number(nvl(T.SPECIFICATIONS,0)) FROM MAS_PRODUCT T WHERE T.ID = B.PRODUCT_ID  and t.CLASSIFY_ID like 'S0106%') SPECIFICATIONS,\n" +
                "                        (SELECT T.STORE_TYPE_NAME FROM MAS_STORE T WHERE T.DEPT_ID = D.BELONG_DEPT_ID ) STORE_TYPE_NAME,D.ID ORDER_ID,\n" +
                "                       D.BELONG_DEPT_ID DEPT_ID, C.SERVICE_PLAN_ID SERVICE_PLAN, D.ID SERVICE_WORK_ORDER_ID, D.ID MAINTENANCE_ORDER_ID,\n" +
                "                       (SELECT T.CAR_LICENSE FROM FN_CUSTOMER_EXPAND T WHERE T.ID = D.FN_CUSTOMER_EXPAND_ID) PLATE_LICENSE,\n" +
                "                       (SELECT T.CLASSIFY_ID FROM MAS_PRODUCT T WHERE T.ID =  B.PRODUCT_ID) CLASSIFY_ID, D.COMPANY_ID,\n" +
                "                       (SELECT T.BRAND_ID FROM MAS_PRODUCT T WHERE T.ID =  B.PRODUCT_ID) BRAND_ID, \n" +
                "                        (SELECT SUBSTR(T.CLASSIFY_ID,1,1) FROM MAS_PRODUCT T WHERE T.ID =  B.PRODUCT_ID)SUB_CLASSIFY,\n" +
                "                        (SELECT SUBSTR(T.CLASSIFY_ID,1,5) FROM MAS_PRODUCT T WHERE T.ID =  B.PRODUCT_ID)SUBSTR_CLASSIFY,\n" +
                "                         (SELECT (SELECT T.ID FROM MAS_ENUMERATION T WHERE T.ID = CO.OPERATION) FROM MAS_CORPORATE CO\n" +
                "                                    WHERE CO.PARTY_ID = (SELECT T.PARTY_ID FROM MAS_PARTY_ROLE T WHERE ID = A.COMPANY_ID)) OPERATION,\n" +
                "                                    (SELECT T.SOURCE_BUSINESS_TYPE_NAME FROM SER_SERVICE_PLAN T WHERE T.ID = C.SERVICE_PLAN_ID) SOURCE_BUSINESS_TYPE_NAME,\n" +
                "                                   TO_CHAR(D.WORKER1_SHOW || '|' || D.WORKER2_SHOW || '|' || D.WORKER3_SHOW || '|' || D.WORKER4_SHOW) THE_WORKER,\n" +
                "                                   TO_CHAR(D.WORKER1) WORKER1, TO_CHAR(D.WORKER2) WORKER2,\n" +
                "                                   TO_CHAR(D.WORKER3) WORKER3,(SELECT SUM(O.AMOUNT)\n" +
                "        FROM  RETAIL_ORDER_PAYMENT_METHOD O,RETAIL_ORDER P\n" +
                "        WHERE P.ORIGIN = C.SERVICE_PLAN_ID\n" +
                "        AND O.RETAIL_ORDER_ID = P.ID\n" +
                "        AND O.PAYMENT_METHOD_TYPE = 10) PAYMENT_METHOD, (CASE WHEN (SELECT Z.ASSESSMENT_COST_PRICE FROM RETAIL_ORDER_ITEM Z,RETAIL_ORDER V WHERE Z.PRODUCT_ID = E.ID  AND Z.SALE_ID = V.ID AND V.ORIGIN = C.SERVICE_PLAN_ID ) = 0 THEN  (SELECT SUM(NVL(Z.TAX_COST_PRICE * Z.PRODUCT_QTY, 0)) \n" +
                "                        FROM ACC_VOUCHER_ITEM Z,ACC_VOUCHER X,RETAIL_ORDER G,RETAIL_ORDER_ITEM V\n" +
                "                        WHERE G.ORIGIN = C.SERVICE_PLAN_ID AND  V.SALE_ID = G.ID AND V.COMPANY_ID = X.COMPANY_ID AND G.ID IN(X.ORIGIN,X.SOURCE) AND Z.VOUCHER_ID = X.ID AND Z.PRODUCT_ID = E.ID AND X.VOUCHER_TYPE = 'CUSTOMER_SALE') ELSE \n" +
                "                        (SELECT Z.ASSESSMENT_COST_PRICE FROM RETAIL_ORDER_ITEM Z,RETAIL_ORDER V WHERE Z.PRODUCT_ID = E.ID  AND Z.SALE_ID = V.ID AND V.ORIGIN = C.SERVICE_PLAN_ID ) END) ASSESSMENT_COST_PRICE,TO_CHAR(D.WORKER4) WORKER4\n" +
                "                   FROM SALE_ORDER A, SALE_ORDER_ITEM B, SER_SERVICE_PLAN_ITEM C, SER_MAINTENANCE_ORDER D ,MAS_PRODUCT E\n" +
                "                   WHERE A.ID = B.SALE_ID\n" +
                "                     AND A.SOURCE = C.SERVICE_PLAN_ID\n" +
                "                     AND B.ORIGIN_ITEM_ID = C.ID\n" +
                "                     AND (C.ID = D.SERVICE_PLAN_ITEM OR C.RELEVANCE_WORK_ORDER = D.ID )\n" +
                "                     AND D.STATE = 'finish'\n" +
                "                     AND B.PRODUCT_ID = E.ID AND E.WORKER_VALID = 1\n" +
                "                     AND ((SELECT t.source_business_type\n" +
                "                                FROM SER_SERVICE_PLAN t\n" +
                "                       WHERE T.ID = C.service_plan_id) IS NULL OR\n" +
                "                        (SELECT t.source_business_type\n" +
                "                                FROM SER_SERVICE_PLAN t\n" +
                "                         WHERE T.ID = C.service_plan_id) != 'LossQuotation')\n" +
                "                     AND A.ID = :BILL_ID\n" +
                "                    -- AND D.BUSINESS_TYPE = 'inner'\n";
//                "                       AND A.DATE_DONE >= to_date('2022-01-01','yyyy-mm-dd') AND A.DATE_DONE < to_date('2022-05-31','yyyy-mm-dd')\n";

        return this.find(sql,param);
    }

    /** 分销单据施工提成 非定损负单*/
    public List<Map> queryFXDJSGTC_FDSFD(JSONObject param){
        checkNull(param,"BILL_ID");

        String sql =  "SELECT IR_TC_DJSGTC.NEXTVAL ID,\n" +
                "      A.DATE_DONE AS SYS_RQ,\n" +
                "      SYSDATE CREATE_DATE,\n" +
                "              A.DATE_DONE TJRQ,\n" +
                "              A.ID OLD_RETAIL_ORDER_ID,\n" +
                "              A.ID RETAIL_ORDER_ID,\n" +
                "              Z.ID THE_RETAIL_ORDER_ID,\n" +
                "              X.ID RETAIL_ITEM_ID,\n" +
                "              Z.ID SERVICE_PLAN,\n" +
                "              B.PRODUCT_ID,\n" +
                "              -X.PRICE_UNIT SALE_AMT,\n" +
                "              B.PRICE_TOTAL OLD_SALE_AMT,\n" +
                "              -X.PRODUCT_RETURN_QTY SALE_QTY,\n" +
                "              B.PRODUCT_UOM_QTY OLD_SALE_QTY,\n" +
                "              B.PRODUCT_UOM_ID OLD_PRODUCT_UOM_ID,\n" +
                "              D.BUSINESS_TYPE,\n" +
                "              (SELECT T.TCJE1\n" +
                "      FROM TC_DJSGTC T\n" +
                "      WHERE T.RETAIL_ORDER_ID = A.ID\n" +
                "      AND T.PRODUCT_ID = B.PRODUCT_ID AND ROWNUM = 1) OLD_TCJE1,\n" +
                "              (SELECT T.TCJE2\n" +
                "      FROM TC_DJSGTC T\n" +
                "      WHERE T.RETAIL_ORDER_ID = A.ID\n" +
                "      AND T.PRODUCT_ID = B.PRODUCT_ID AND ROWNUM = 1) OLD_TCJE2,\n" +
                "              (SELECT T.TCJE3\n" +
                "      FROM TC_DJSGTC T\n" +
                "      WHERE T.RETAIL_ORDER_ID = A.ID\n" +
                "      AND T.PRODUCT_ID = B.PRODUCT_ID AND ROWNUM = 1) OLD_TCJE3,\n" +
                "              (SELECT T.TCJE4\n" +
                "      FROM TC_DJSGTC T\n" +
                "      WHERE T.RETAIL_ORDER_ID = A.ID\n" +
                "      AND T.PRODUCT_ID = B.PRODUCT_ID AND ROWNUM = 1) OLD_TCJE4,\n" +
                "              (SELECT B.SERVE_TYPE_ID\n" +
                "      FROM MAS_SERVE_CATEGORY B, MAS_PRODUCT C\n" +
                "      WHERE C.CLASSIFY_ID = B.CLASSIFY_ID\n" +
                "      AND C.ID = B.PRODUCT_ID\n" +
                "      and rownum = 1) SERVE_TYPE_ID,\n" +
                "              (SELECT to_number(nvl(-T.SPECIFICATIONS, 0))\n" +
                "      FROM MAS_PRODUCT T\n" +
                "      WHERE T.ID = B.PRODUCT_ID\n" +
                "      and t.CLASSIFY_ID like 'S0106%') SPECIFICATIONS,\n" +
                "              (SELECT T.STORE_TYPE_NAME\n" +
                "      FROM MAS_STORE T\n" +
                "      WHERE T.DEPT_ID = D.BELONG_DEPT_ID) STORE_TYPE_NAME,\n" +
                "              D.ID ORDER_ID,\n" +
                "              D.BELONG_DEPT_ID DEPT_ID,\n" +
                "              D.ID SERVICE_WORK_ORDER_ID,\n" +
                "              D.ID MAINTENANCE_ORDER_ID,\n" +
                "              (SELECT T.CAR_LICENSE\n" +
                "      FROM FN_CUSTOMER_EXPAND T\n" +
                "      WHERE T.ID = D.FN_CUSTOMER_EXPAND_ID) PLATE_LICENSE,\n" +
                "              (SELECT T.CLASSIFY_ID FROM MAS_PRODUCT T WHERE T.ID = B.PRODUCT_ID) CLASSIFY_ID,\n" +
                "              (SELECT T.BRAND_ID FROM MAS_PRODUCT T WHERE T.ID = B.PRODUCT_ID) BRAND_ID,\n" +
                "              D.COMPANY_ID,\n" +
                "              (SELECT SUBSTR(T.CLASSIFY_ID, 1, 1)\n" +
                "      FROM MAS_PRODUCT T\n" +
                "      WHERE T.ID = B.PRODUCT_ID) SUB_CLASSIFY,\n" +
                "              (SELECT SUBSTR(T.CLASSIFY_ID, 1, 5)\n" +
                "      FROM MAS_PRODUCT T\n" +
                "      WHERE T.ID = B.PRODUCT_ID) SUBSTR_CLASSIFY,\n" +
                "              (SELECT (SELECT T.ID FROM MAS_ENUMERATION T WHERE T.ID = CO.OPERATION)\n" +
                "      FROM MAS_CORPORATE CO\n" +
                "      WHERE CO.PARTY_ID = (SELECT T.PARTY_ID\n" +
                "      FROM MAS_PARTY_ROLE T\n" +
                "      WHERE ID = A.COMPANY_ID)) OPERATION,\n" +
                "              (SELECT T.SOURCE_BUSINESS_TYPE_NAME\n" +
                "      FROM SER_SERVICE_PLAN T\n" +
                "      WHERE T.ID = C.SERVICE_PLAN_ID) SOURCE_BUSINESS_TYPE_NAME,\n" +
                "              TO_CHAR(D.WORKER1_SHOW || '|' || D.WORKER2_SHOW || '|' || D.WORKER3_SHOW || '|' || D.WORKER4_SHOW) THE_WORKER,\n" +
                "              TO_CHAR(D.WORKER1) WORKER1,\n" +
                "              TO_CHAR(D.WORKER2) WORKER2,\n" +
                "              TO_CHAR(D.WORKER3) WORKER3,\n" +
                "              TO_CHAR(D.WORKER4) WORKER4\n" +
                "      FROM SALE_ORDER                A,\n" +
                "              SALE_ORDER_ITEM           B,\n" +
                "              SER_SERVICE_PLAN_ITEM     C,\n" +
                "              SER_MAINTENANCE_ORDER     D,\n" +
                "              MAS_PRODUCT               E,\n" +
                "              internal_help_return      Z,\n" +
                "              internal_help_return_item X\n" +
                "      WHERE A.ID = B.SALE_ID\n" +
                "      AND Z.ORIGIN = A.ID\n" +
                "      AND A.SOURCE = C.SERVICE_PLAN_ID\n" +
                "      AND B.ORIGIN_ITEM_ID = C.ID\n" +
                "      AND (C.ID = D.SERVICE_PLAN_ITEM OR C.RELEVANCE_WORK_ORDER = D.ID)\n" +
                "     AND D.STATE = 'finish'\n" +
                "      AND B.PRODUCT_ID = E.ID\n" +
                "      AND E.WORKER_VALID = 1\n" +
                "      AND ((SELECT t.source_business_type\n" +
                "              FROM SER_SERVICE_PLAN t\n" +
                "              WHERE T.ID = C.service_plan_id) IS NULL OR\n" +
                "              (SELECT t.source_business_type\n" +
                "                      FROM SER_SERVICE_PLAN t\n" +
                "                      WHERE T.ID = C.service_plan_id) != 'LossQuotation')\n" +
                "      AND X.RETURN_ID = Z.ID\n" +
                "      AND X.PRODUCT_ID = E.ID\n" +
                "      AND Z.ID = :BILL_ID\n";
        return this.find(sql,param);
    }


    /** 分销单据施工提成 定损*/
    public List<Map> queryFXDJSGTC_DS(JSONObject param){
        checkNull(param,"BILL_ID");

        String sql = "SELECT IR_TC_DJSGTC.NEXTVAL ID,SYSDATE CREATE_DATE, A.DATE_DONE AS SYS_RQ, A.DATE_DONE TJRQ, A.ID RETAIL_ORDER_ID,B.ID RETAIL_ITEM_ID,\n" +
                "                         B.PRODUCT_ID, (CASE WHEN (SELECT Z.ASSESSMENT_COST_PRICE FROM RETAIL_ORDER_ITEM Z,RETAIL_ORDER V WHERE Z.PRODUCT_ID = E.ID  AND Z.SALE_ID = V.ID AND V.ORIGIN = C.SERVICE_PLAN_ID ) = 0 THEN  (SELECT SUM(NVL(Z.TAX_COST_PRICE * Z.PRODUCT_QTY, 0)) \n" +
                "FROM ACC_VOUCHER_ITEM Z,ACC_VOUCHER X,RETAIL_ORDER G,RETAIL_ORDER_ITEM V\n" +
                "WHERE G.ORIGIN = C.SERVICE_PLAN_ID AND  V.SALE_ID = G.ID AND V.COMPANY_ID = X.COMPANY_ID AND G.ID IN(X.ORIGIN,X.SOURCE) AND Z.VOUCHER_ID = X.ID AND Z.PRODUCT_ID = E.ID AND X.VOUCHER_TYPE = 'CUSTOMER_SALE') ELSE \n" +
                "(SELECT Z.ASSESSMENT_COST_PRICE FROM RETAIL_ORDER_ITEM Z,RETAIL_ORDER V WHERE Z.PRODUCT_ID = E.ID  AND Z.SALE_ID = V.ID AND V.ORIGIN = C.SERVICE_PLAN_ID ) END) ASSESSMENT_COST_PRICE,B.PRICE_TOTAL SALE_AMT, B.PRODUCT_UOM_QTY SALE_QTY, B.PRODUCT_UOM_ID, D.BUSINESS_TYPE,\n" +
                "                          (SELECT B.SERVE_TYPE_ID FROM MAS_SERVE_CATEGORY B, MAS_PRODUCT C\n" +
                "                                      WHERE C.CLASSIFY_ID = B.CLASSIFY_ID AND C.ID =  B.PRODUCT_ID and rownum =1) SERVE_TYPE_ID,\n" +
                "                        (SELECT to_number(nvl(T.SPECIFICATIONS,0)) FROM MAS_PRODUCT T WHERE T.ID = B.PRODUCT_ID  and t.CLASSIFY_ID like 'S0106%') SPECIFICATIONS,\n" +
                "                        (SELECT T.STORE_TYPE_NAME FROM MAS_STORE T WHERE T.DEPT_ID = D.BELONG_DEPT_ID ) STORE_TYPE_NAME,D.ID ORDER_ID,\n" +
                "                       D.BELONG_DEPT_ID DEPT_ID, C.SERVICE_PLAN_ID SERVICE_PLAN, D.ID SERVICE_WORK_ORDER_ID, D.ID MAINTENANCE_ORDER_ID,\n" +
                "                       (SELECT T.CAR_LICENSE FROM FN_CUSTOMER_EXPAND T WHERE T.ID = D.FN_CUSTOMER_EXPAND_ID) PLATE_LICENSE,\n" +
                "                       (SELECT T.BRAND_ID FROM MAS_PRODUCT T WHERE T.ID =  B.PRODUCT_ID) BRAND_ID,\n" +
                "                       (SELECT T.CLASSIFY_ID FROM MAS_PRODUCT T WHERE T.ID =  B.PRODUCT_ID) CLASSIFY_ID,(SELECT SUM(O.AMOUNT)\n" +
                "        FROM  RETAIL_ORDER_PAYMENT_METHOD O,RETAIL_ORDER P\n" +
                "        WHERE P.ORIGIN = C.SERVICE_PLAN_ID\n" +
                "        AND O.RETAIL_ORDER_ID = P.ID\n" +
                "        AND O.PAYMENT_METHOD_TYPE = 10) PAYMENT_METHOD, D.COMPANY_ID,\n" +
                "                        (SELECT SUBSTR(T.CLASSIFY_ID,1,1) FROM MAS_PRODUCT T WHERE T.ID =  B.PRODUCT_ID)SUB_CLASSIFY,\n" +
                "                        (SELECT SUBSTR(T.CLASSIFY_ID,1,5) FROM MAS_PRODUCT T WHERE T.ID =  B.PRODUCT_ID)SUBSTR_CLASSIFY,\n" +
                "                         (SELECT (SELECT T.ID FROM MAS_ENUMERATION T WHERE T.ID = CO.OPERATION) FROM MAS_CORPORATE CO\n" +
                "                                    WHERE CO.PARTY_ID = (SELECT T.PARTY_ID FROM MAS_PARTY_ROLE T WHERE ID = A.COMPANY_ID)) OPERATION,\n" +
                "                                    (SELECT T.SOURCE_BUSINESS_TYPE_NAME FROM SER_SERVICE_PLAN T WHERE T.ID = C.SERVICE_PLAN_ID) SOURCE_BUSINESS_TYPE_NAME,\n" +
                "                                   TO_CHAR(D.WORKER1_SHOW || '|' || D.WORKER2_SHOW || '|' || D.WORKER3_SHOW || '|' || D.WORKER4_SHOW) THE_WORKER,\n" +
                "                                   TO_CHAR(D.WORKER1) WORKER1, TO_CHAR(D.WORKER2) WORKER2,\n" +
                "                                   TO_CHAR(D.WORKER3) WORKER3, TO_CHAR(D.WORKER4) WORKER4\n" +
                "                   FROM SALE_ORDER A, SALE_ORDER_ITEM B, SER_SERVICE_PLAN_ITEM C, SER_MAINTENANCE_ORDER D ,MAS_PRODUCT E\n" +
                "                   WHERE A.ID = B.SALE_ID AND B.ORIGIN_ITEM_ID = C.ID  AND (C.ID = D.SERVICE_PLAN_ITEM OR C.RELEVANCE_WORK_ORDER = D.ID )\n" +
                "                     AND D.STATE = 'finish'\n" +
                "                     AND B.PRODUCT_ID = E.ID AND E.WORKER_VALID = 1" +
                "                     AND (SELECT t.source_business_type\n" +
                "                                FROM SER_SERVICE_PLAN t\n" +
                "                         WHERE T.ID = C.service_plan_id) = 'LossQuotation'\n" +
                "                     AND A.ID = :BILL_ID\n" +
                "                    -- AND D.BUSINESS_TYPE = 'inner'\n";
//                "                       AND A.DATE_DONE >= to_date('2022-01-01','yyyy-mm-dd') AND A.DATE_DONE < to_date('2022-05-31','yyyy-mm-dd')\n";

        return this.find(sql,param);
    }


    /** 分销单据施工提成 定损负单*/
    public List<Map> queryFXDJSGTC_DSFD(JSONObject param){
        checkNull(param,"BILL_ID");

        String sql =  "SELECT IR_TC_DJSGTC.NEXTVAL ID,\n" +
                "      A.DATE_DONE AS SYS_RQ,\n" +
                "              A.DATE_DONE TJRQ,\n" +
                "              A.ID OLD_RETAIL_ORDER_ID,\n" +
                "              A.ID RETAIL_ORDER_ID,\n" +
                "              Z.ID THE_RETAIL_ORDER_ID,\n" +
                "              X.ID RETAIL_ITEM_ID,\n" +
                "              Z.ID  SERVICE_PLAN,\n" +
                "              B.PRODUCT_ID,\n" +
                "              SYSDATE CREATE_DATE,\n" +
                "              -X.PRICE_UNIT SALE_AMT,\n" +
                "              B.PRICE_TOTAL OLD_SALE_AMT,\n" +
                "              -X.PRODUCT_RETURN_QTY SALE_QTY,\n" +
                "              B.PRODUCT_UOM_QTY OLD_SALE_QTY,\n" +
                "              B.PRODUCT_UOM_ID OLD_PRODUCT_UOM_ID,\n" +
                "              D.BUSINESS_TYPE,\n" +
                "              (SELECT T.TCJE1\n" +
                "      FROM TC_DJSGTC T\n" +
                "      WHERE T.RETAIL_ORDER_ID = A.ID\n" +
                "      AND T.PRODUCT_ID = B.PRODUCT_ID AND ROWNUM = 1) OLD_TCJE1,\n" +
                "              (SELECT T.TCJE2\n" +
                "      FROM TC_DJSGTC T\n" +
                "      WHERE T.RETAIL_ORDER_ID = A.ID\n" +
                "      AND T.PRODUCT_ID = B.PRODUCT_ID AND ROWNUM = 1) OLD_TCJE2,\n" +
                "              (SELECT T.TCJE3\n" +
                "      FROM TC_DJSGTC T\n" +
                "      WHERE T.RETAIL_ORDER_ID = A.ID\n" +
                "      AND T.PRODUCT_ID = B.PRODUCT_ID AND ROWNUM = 1) OLD_TCJE3,\n" +
                "              (SELECT T.TCJE4\n" +
                "      FROM TC_DJSGTC T\n" +
                "      WHERE T.RETAIL_ORDER_ID = A.ID\n" +
                "      AND T.PRODUCT_ID = B.PRODUCT_ID AND ROWNUM = 1) OLD_TCJE4,\n" +
                "              (SELECT B.SERVE_TYPE_ID\n" +
                "      FROM MAS_SERVE_CATEGORY B, MAS_PRODUCT C\n" +
                "      WHERE C.CLASSIFY_ID = B.CLASSIFY_ID\n" +
                "      AND C.ID = B.PRODUCT_ID\n" +
                "      and rownum = 1) SERVE_TYPE_ID,\n" +
                "              (SELECT to_number(nvl(-T.SPECIFICATIONS, 0))\n" +
                "      FROM MAS_PRODUCT T\n" +
                "      WHERE T.ID = B.PRODUCT_ID\n" +
                "      and t.CLASSIFY_ID like 'S0106%') SPECIFICATIONS,\n" +
                "              (SELECT T.STORE_TYPE_NAME\n" +
                "      FROM MAS_STORE T\n" +
                "      WHERE T.DEPT_ID = D.BELONG_DEPT_ID) STORE_TYPE_NAME,\n" +
                "              D.ID ORDER_ID,\n" +
                "              D.BELONG_DEPT_ID DEPT_ID,\n" +
                "              D.ID SERVICE_WORK_ORDER_ID,\n" +
                "              D.ID MAINTENANCE_ORDER_ID,\n" +
                "              (SELECT T.CAR_LICENSE\n" +
                "      FROM FN_CUSTOMER_EXPAND T\n" +
                "      WHERE T.ID = D.FN_CUSTOMER_EXPAND_ID) PLATE_LICENSE,\n" +
                "              (SELECT T.BRAND_ID FROM MAS_PRODUCT T WHERE T.ID = B.PRODUCT_ID) BRAND_ID,\n" +
                "              (SELECT T.CLASSIFY_ID FROM MAS_PRODUCT T WHERE T.ID = B.PRODUCT_ID) CLASSIFY_ID,\n" +
                "              D.COMPANY_ID,\n" +
                "              (SELECT SUBSTR(T.CLASSIFY_ID, 1, 1)\n" +
                "      FROM MAS_PRODUCT T\n" +
                "      WHERE T.ID = B.PRODUCT_ID) SUB_CLASSIFY,\n" +
                "              (SELECT SUBSTR(T.CLASSIFY_ID, 1, 5)\n" +
                "      FROM MAS_PRODUCT T\n" +
                "      WHERE T.ID = B.PRODUCT_ID) SUBSTR_CLASSIFY,\n" +
                "              (SELECT (SELECT T.ID FROM MAS_ENUMERATION T WHERE T.ID = CO.OPERATION)\n" +
                "      FROM MAS_CORPORATE CO\n" +
                "      WHERE CO.PARTY_ID = (SELECT T.PARTY_ID\n" +
                "      FROM MAS_PARTY_ROLE T\n" +
                "      WHERE ID = A.COMPANY_ID)) OPERATION,\n" +
                "              (SELECT T.SOURCE_BUSINESS_TYPE_NAME\n" +
                "      FROM SER_SERVICE_PLAN T\n" +
                "      WHERE T.ID = C.SERVICE_PLAN_ID) SOURCE_BUSINESS_TYPE_NAME,\n" +
                "              TO_CHAR(D.WORKER1_SHOW || '|' || D.WORKER2_SHOW || '|' || D.WORKER3_SHOW || '|' || D.WORKER4_SHOW) THE_WORKER,\n" +
                "              TO_CHAR(D.WORKER1) WORKER1,\n" +
                "              TO_CHAR(D.WORKER2) WORKER2,\n" +
                "              TO_CHAR(D.WORKER3) WORKER3,\n" +
                "              TO_CHAR(D.WORKER4) WORKER4\n" +
                "      FROM SALE_ORDER                A,\n" +
                "              SALE_ORDER_ITEM           B,\n" +
                "              SER_SERVICE_PLAN_ITEM     C,\n" +
                "              SER_MAINTENANCE_ORDER     D,\n" +
                "              MAS_PRODUCT               E,\n" +
                "              internal_help_return      Z,\n" +
                "              internal_help_return_item X\n" +
                "      WHERE A.ID = B.SALE_ID\n" +
                "      AND Z.ORIGIN = A.ID\n" +
                "      AND A.SOURCE = C.SERVICE_PLAN_ID\n" +
                "      AND B.ORIGIN_ITEM_ID = C.ID\n" +
                "      AND (C.ID = D.SERVICE_PLAN_ITEM OR C.RELEVANCE_WORK_ORDER = D.ID)\n" +
                "     AND D.STATE = 'finish'\n" +
                "      AND B.PRODUCT_ID = E.ID\n" +
                "      AND E.WORKER_VALID = 1\n" +
                "      AND (SELECT t.source_business_type\n" +
                "      FROM SER_SERVICE_PLAN t\n" +
                "      WHERE T.ID = C.service_plan_id) = 'LossQuotation'\n" +
                "      AND X.RETURN_ID = Z.ID\n" +
                "      AND X.PRODUCT_ID = E.ID\n" +
                "      AND Z.ID = :BILL_ID\n";
        return this.find(sql,param);
    }

    /**异业开单提成零售负单 原提成金额*/
    public JSONArray queryYYKDTCOLDTCJE(JSONObject param){
        checkNull(param,"OLD_RETAIL_ORDER_ID","DEPT_ID");

        String sql = "SELECT T.TC_AMT AS OLE_TCJE FROM TC_KDTC T WHERE T.RETAIL_ORDER_ID = :OLD_RETAIL_ORDER_ID AND T.DEPT_ID = :DEPT_ID AND T.TYPE = 'YY_KD_TC'";
        return toJSONArray(this.find(sql, param));
    }

    /**查询人员总份数*/
    public JSONArray queryRYFS(JSONObject param) throws Exception{

        String sql = "SELECT SUM(STAFF_SHARE) AS PORTION FROM HR_EMPLOYEE WHERE ORIGIN in " + getInParam(format(param.getJSONArray("WORKER_ARRAY")));
        return toJSONArray(this.find(sql, param));
    }

    /**查询人员施工提成ID*/
    public JSONArray queryRYSGTCID(JSONObject param) throws Exception{

        String sql = "SELECT IR_TC_RYSGTC.NEXTVAL ID FROM DUAL";
        return toJSONArray(this.find(sql, param));
    }

    /**查询销售提成ID*/
    public JSONArray queryXSTCID(JSONObject param){

        String sql = "SELECT IR_TC_XSTC.NEXTVAl ID FROM DUAL";
        return toJSONArray(this.find(sql, param));
    }

    /** 判断是否存在 /*/
    public JSONArray existXSTC(JSONObject param){
        checkNull(param,"TJRQ","RETAIL_ORDER_ID","DEPT_ID","PRODUCT_ID");

        String sql = " SELECT COUNT(1) CZBJ FROM TC_XSETC T WHERE TRUNC(T.TJRQ) = TRUNC(:TJRQ) " +
                "AND T.RETAIL_ORDER_ID = :RETAIL_ORDER_ID " +
                "AND T.DEPT_ID = :DEPT_ID AND T.PRODUCT_ID = :PRODUCT_ID ";
        return toJSONArray(this.find(sql, param));
    }




    /** 判断是否存在 /*/
    public JSONArray existXSTCKD(JSONObject param){
        checkNull(param,"TJRQ","RETAIL_ORDER_ID","DEPT_ID");

        String sql = " SELECT COUNT(1) CZBJ FROM TC_XSETC T WHERE TRUNC(T.TJRQ) = TRUNC(:TJRQ) " +
                "AND T.RETAIL_ORDER_ID = :RETAIL_ORDER_ID " +
                "AND T.DEPT_ID = :DEPT_ID";
        return toJSONArray(this.find(sql, param));
    }

    /** （有套包）年卡提成判断是否存在 */
    public JSONArray existNKTC(JSONObject param){
        checkNull(param,"TJRQ","RETAIL_ORDER_ID");

        String sql = " SELECT COUNT(1) CZBJ FROM TC_NKTC T WHERE TRUNC(T.TJRQ) = TRUNC(:TJRQ) AND T.RETAIL_ORDER_ID = :RETAIL_ORDER_ID";
        return toJSONArray(this.find(sql, param));
    }

    /**（无套包）年卡销售提成判断是否存在 /*/
    public JSONArray existNKXSTC(JSONObject param){
        checkNull(param,"TJRQ","SOURCE");

        String sql = "SELECT NVL(COUNT(1),0) CZBJ FROM TC_XSETC T WHERE TRUNC(T.TJRQ) = TRUNC(:TJRQ) AND T.RETAIL_ORDER_ID = :SOURCE";
        return toJSONArray(this.find(sql, param));
    }

    /** 接送车提成判断是否存在 */
    public JSONArray existJSCTC(JSONObject param){
        checkNull(param,"FN_INNER_PICK_DEL_UP_CAR_ID","JSCR","BZ");

        String sql = "SELECT COUNT(1) CZBJ FROM TC_JSCTC T WHERE T.FN_INNER_PICK_DEL_UP_CAR_ID = :FN_INNER_PICK_DEL_UP_CAR_ID AND T.JSCR = :JSCR AND T.BZ = :BZ";
        return toJSONArray(this.find(sql, param));
    }

    /** 内协开单提成判断是否存在 */
    public JSONArray existKDTC(JSONObject param){
        checkNull(param,"FN_INNER_PICK_DEL_UP_CAR_ID","BILL_MAN");

        String sql = "SELECT COUNT(1) CZBJ FROM TC_KDTC T WHERE T.FN_INNER_PICK_DEL_UP_CAR_ID = :FN_INNER_PICK_DEL_UP_CAR_ID AND T.BILL_MAN = :BILL_MAN";
        return toJSONArray(this.find(sql, param));
    }

    /** 内协开单 判断是否存在工单内标记 */
    public JSONArray existNXKDGDNBJ(JSONObject param){
        checkNull(param,"FN_INNER_PICK_DEL_UP_CAR_ID");

        String sql = "SELECT COUNT(1) GDCZBJ FROM SER_SERVICE_PLAN T WHERE T.FN_CUSTOMER_EXPAND_ID = :FN_INNER_PICK_DEL_UP_CAR_ID AND T.PLAN_TYPE = 'someAssist' AND T.QUALITY_TEST_DATE IS NOT NULL";
        return toJSONArray(this.find(sql, param));
    }

    /** 异业开单提成零售正单 判断是否存在 */
    public JSONArray existYYKDTCZD(JSONObject param){
        checkNull(param,"TJRQ","RETAIL_ORDER_ID","DEPT_ID","PLATE_LICENSE");

        String sql = "SELECT COUNT(1) CZBJ FROM TC_KDTC T WHERE (TRUNC(T.TJRQ) = TRUNC(:TJRQ) AND T.RETAIL_ORDER_ID = :RETAIL_ORDER_ID AND T.DEPT_ID = :DEPT_ID " +
                "AND T.TYPE = 'YY_KD_TC') OR (T.PLATE_LICENSE = :PLATE_LICENSE AND TRUNC(T.TJRQ) = TRUNC(:TJRQ) AND T.TYPE = 'YY_KD_TC')";
        return toJSONArray(this.find(sql, param));
    }

    /** 代勘提成定损完成 判断是否存在 */
    public JSONArray existDKTC(JSONObject param){
        checkNull(param,"TJRQ","RETAIL_ORDER_ID","TYPE");

        String sql = "SELECT COUNT(1) CZBJ FROM TC_KDTC T WHERE TRUNC(T.TJRQ) = TRUNC(:TJRQ) AND T.RETAIL_ORDER_ID = :RETAIL_ORDER_ID AND T.TYPE = :TYPE";
        return toJSONArray(this.find(sql, param));
    }

    /** 定损销售额提成 接案介绍费 判断是否存在 */
    public JSONArray existDSXSETC(JSONObject param){
        checkNull(param,"TJRQ","SOURCE","TYPE");

        String sql = " SELECT COUNT(1) CZBJ FROM TC_DSXSETC T WHERE TRUNC(T.TJRQ) = TRUNC(:TJRQ) AND T.SOURCE = :SOURCE AND T.TYPE = :TYPE ";
        return toJSONArray(this.find(sql, param));
    }

    /** 定损销售额提成 定损服务费 判断是否存在 */
    public JSONArray existDSFWTC(JSONObject param){
        checkNull(param,"TJRQ","SOURCE");

        String sql = " SELECT COUNT(1) CZBJ FROM TC_DSXSETC T WHERE TRUNC(T.TJRQ) =TRUNC(:TJRQ) AND T.SOURCE =:SOURCE AND T.TYPE = '02' ";
        return toJSONArray(this.find(sql, param));
    }

    /** 定损销售额提成 车险提成规则 判断是否存在 */
    public JSONArray existCXTC(JSONObject param){
        checkNull(param,"TJRQ","ID");

        String sql = " SELECT COUNT(1) CZBJ FROM TC_CXTC T WHERE TRUNC(T.TJRQ) =TRUNC(:TJRQ) AND T.ID =:ID ";
        return toJSONArray(this.find(sql, param));
    }

    /** 定损销售额提成 定损跟踪提成_新系统 判断是否存在 */
    public JSONArray existNewDSGZTC(JSONObject param){
        checkNull(param,"TJRQ","CAR_ACCIDENT_ID");

        String sql = "  SELECT COUNT(1) CZBJ FROM TC_DSGZTC T WHERE TRUNC(T.TJRQ) =TRUNC(:TJRQ) AND T.CAR_ACCIDENT_ID =:CAR_ACCIDENT_ID ";
        return toJSONArray(this.find(sql, param));
    }

    /** 定损销售额提成 定损跟踪提成_老系统 判断是否存在 */
    public JSONArray existOldDSGZTC(JSONObject param){
        checkNull(param,"TJRQ","CAR_ACCIDENT_ID");

        String sql = "  SELECT COUNT(1) CZBJ FROM TC_DSGZTC T WHERE TRUNC(T.TJRQ) =TRUNC(:TJRQ) AND T.CAR_ACCIDENT_ID =:CAR_ACCIDENT_ID ";
        return toJSONArray(this.find(sql, param));
    }

    /** 单据施工提成明细 判断是否存在 */
    public JSONArray existDJSGTC(JSONObject param){
        checkNull(param,"TJRQ","RETAIL_ORDER_ID","DEPT_ID","PRODUCT_ID");

        String sql = "SELECT COUNT(1) CZBJ FROM TC_DJSGTC T WHERE TRUNC(T.TJRQ) = TRUNC(:TJRQ) AND T.RETAIL_ORDER_ID = :RETAIL_ORDER_ID" +
                " AND T.DEPT_ID = :DEPT_ID AND T.PRODUCT_ID = :PRODUCT_ID";
        return toJSONArray(this.find(sql, param));
    }




    /** 人员施工提成明细 判断是否存在 */
    public JSONArray existRYSGTC(JSONObject param){
        checkNull(param,"WORKER","RETAIL_ORDER_ID","PRODUCT_ID","PROCESS");

        String sql = "SELECT COUNT(1) CZBJ FROM TC_RYSGTC T WHERE T.WORKER = :WORKER AND T.RETAIL_ORDER_ID = :RETAIL_ORDER_ID " +
                "AND T.PRODUCT_ID = :PRODUCT_ID AND T.PROCESS = :PROCESS";
        return toJSONArray(this.find(sql, param));
    }

    /** 插入销售提成 */
    public void insertXSTC(JSONObject param) {
        String sSQL = "INSERT INTO TC_XSETC (ID, TJRQ, RETAIL_ORDER_ID, PLATE_LICENSE, SERVE_TYPE_ID," +
                " SOURCE, SOURCE_BUSINESS_TYPE, COMPANY_ID, DEPT_ID, PRODUCT_ID, " +
                "CLASSIFY_ID, SALE_QTY, PRODUCT_UOM_ID, SALE_AMT, YXSDJ, YYHDJ, " +
                "LOCK_PRICE, ASSESSMENT_COST_PRICE, PROFIT_AMT, REFER_TAX, SUPPLY_CHAIN_MANAGEMENT, " +
                "ESTIMATED_COMMISSION, NKDK_AMT, HBDK_AMT,NEWHBDK_AMT,NEWBB_AMT, FXDK_AMT, DISCOUNT_AMOUNT, TCJE, " +
                "MAIN_SALESMAN, SUB_SALESMAN, MAIN_SALESMAN_TCJE, SUB_SALESMAN_TCJE, " +
                "SUB_SALESMAN_PORTION, MAIN_SALESMAN_PORTION, TCBL_JE,CLH,CREATE_DATE)" +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?,?,?,?,?)";

        executeInsert(sSQL, MapUtil.mapped(param));
    }

    /** 插入 年卡提成 */
    public void insertNKTC(JSONObject param) {
        String sSQL = "INSERT INTO TC_NKTC " +
                "(ID, TJRQ, JCRQ, RETAIL_ORDER_ID, COMPANY_ID, DEPT_ID, SALESMAN, SUB_SALESMAN, SALE_AMT, TC_AMT, MAIN_SALESMAN_TCJE,SUB_SALESMAN_TCJE,PLATE_LICENSE,CREATE_DATE) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)";

        executeInsert(sSQL, MapUtil.mapped(param));
    }

    /** 插入接送车提成 */
    public void insertJSCTC(JSONObject param) {
        String sSQL = "INSERT INTO TC_JSCTC " +
                "(ID, TJRQ, JCRQ, FN_INNER_PICK_DEL_UP_CAR_ID, PLATE_LICENSE, COMPANY_ID, DEPT_ID_MD, DEPT_ID_JD, JSCR, BZ, TC_AMT,DISPATCHER_DEPT_ID,COMMISSION_FLAG,CREATE_DATE)" +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,? , ?,?)";

        executeInsert(sSQL, MapUtil.mapped(param));
    }

    /** 插入开单提成 */
    public void insertKDTC(JSONObject param) {
        String sSQL = "INSERT INTO TC_KDTC\n" +
                "  (ID, TJRQ, JCRQ, FN_INNER_PICK_DEL_UP_CAR_ID, PLATE_LICENSE, COMPANY_ID, DEPT_ID, BILL_MAN, TC_AMT, SERVE_TYPE_ID,\n" +
                "   RETAIL_ORDER_ID, SOURCE, P_CROSS_ID, CROSS_COMPANY_ID, TYPE,CREATE_DATE)" +
                "VALUES (?,?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?,?)";

        executeInsert(sSQL, MapUtil.mapped(param));
    }

    /** 插入 定损销售提成 */
    public void insertDSXSETC(JSONObject param) {
        String sSQL = "INSERT INTO TC_DSXSETC" +
                "(ID, TJRQ, PLATE_LICENSE, SOURCE, SOURCE_BUSINESS_TYPE, COMPANY_ID, DEPT_ID, AMOUNT, LOSS_QUOTATION_AMT, TCJE,GET_CAR_UID, TYPE,CREATE_DATE)" +
                "VALUES  (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?,?)";

        executeInsert(sSQL, MapUtil.mapped(param));
    }

    /** 插入 定损销售提成 定损服务费 */
    public void insertDSFWTC(JSONObject param) {
        String sSQL = "INSERT INTO TC_DSXSETC " +
                "(ID, TJRQ, PLATE_LICENSE, SOURCE, SOURCE_BUSINESS_TYPE, COMPANY_ID, DEPT_ID, AMOUNT, LOSS_QUOTATION_AMT, TCJE, GET_CAR_UID, TYPE,CREATE_DATE)" +
                " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)";

        executeInsert(sSQL, MapUtil.mapped(param));
    }


    /** 插入 定损销售提成 车险提成规则 */
    public void insertCXTC(JSONObject param) {
        String sSQL = "INSERT INTO TC_CXTC " +
                "(TJRQ, DEPT_ID, COMPANY_ID, ID, PLATE_LICENSE, INSURANCE_TYPE, NET_PREMIUM, SALESMAN_ID, FN_PROFIT_RATIO, FN_PROFIT, FN_COMMISSION, CXJYE, SUPPLIER_ID, CITY, PROVINCE, USE, CAR_OWNERSHIP, RENEWAL, START_DATE, END_DATE, ITEM_THIRD_PRICE,CREATE_DATE) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)\n";

        executeInsert(sSQL, MapUtil.mapped(param));
    }


    /** 插入 定损销售提成 定损跟踪提成_新系统 */
    public void insertNewDSGZTC(JSONObject param) {
        String sSQL = "INSERT INTO TC_DSGZTC " +
                "(TJRQ, DEPT_ID, CAR_ACCIDENT_ID, LOSS_QUOTATION_ID, PLATE_LICENSE, RETAIL_ORDER_ID, CASE_TYPE, INSURANCE_COMPANY_ID, STAFF_ID, TCJE, COMPANY_ID,CREATE_DATE) " +
                "VALUES (?, ?, ?,?, ?, ?, ?, ?, ?,?,?,?)";

        executeInsert(sSQL, MapUtil.mapped(param));
    }

    /** 插入 定损销售提成 定损跟踪提成_老系统 */
    public void insertOldDSGZTC(JSONObject param) {
        String sSQL = "INSERT INTO TC_DSGZTC " +
                "(TJRQ, DEPT_ID, CAR_ACCIDENT_ID, LOSS_QUOTATION_ID, PLATE_LICENSE, RETAIL_ORDER_ID, CASE_TYPE, INSURANCE_COMPANY_ID, STAFF_ID, TCJE, COMPANY_ID,CREATE_DATE) " +
                "VALUES (?, ?, ?,?, ?, ?, ?, ?, ?,?,?,?)";

        executeInsert(sSQL, MapUtil.mapped(param));
    }

    /** 插入单据施工提成 */
    public void insertDJSGTC(JSONObject param) {
        String sSQL = "INSERT INTO TC_DJSGTC\n" +
                "  (ID, TJRQ, RETAIL_ORDER_ID, SERVICE_PLAN, SERVE_TYPE_ID, SERVICE_WORK_ORDER_ID, PLATE_LICENSE, COMPANY_ID, DEPT_ID,\n" +
                "   PRODUCT_ID, CLASSIFY_ID, SPECIFICATIONS, SALE_QTY, SALE_AMT, WORKER1, WORKER2, WORKER3, WORKER4, TCJE1, TCJE2, TCJE3,\n" +
                "   TCJE4, TCBL_JE1, TCBL_JE2, TCBL_JE3, TCBL_JE4,ASSESSMENT_COST_PRICE,PAYMENT_METHOD,CREATE_DATE)\n" +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?)";

        executeInsert(sSQL, MapUtil.mapped(param));
    }

    /** 插入人员施工提成 */
    public void insertRYSGTC(JSONObject param) {
        String sSQL = "INSERT INTO TC_RYSGTC\n" +
                "  (ID, TJRQ, RETAIL_ORDER_ID, SERVICE_PLAN, ORDER_ID, SERVE_TYPE_ID, PLATE_LICENSE, COMPANY_ID, DEPT_ID, WORKER,\n" +
                "   PRODUCT_ID, CLASSIFY_ID, SPECIFICATIONS, SALE_AMT, PROCESS, PORTION, TC_AMT, SALE_QTY,CREATE_DATE)\n" +
                "VALUES (?, ?, ?, ?,?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)";

        executeInsert(sSQL, MapUtil.mapped(param));
    }


    /**
     * 执行插入数据
     * @param sql 根据第一个括号的内容截取要插入的字段
     */
    public void executeInsert(String sql, Map<String,Object> param){
        Connection conn;
        PreparedStatement stmt = null;
        try {
            conn = connector.getConnection();
            stmt = conn.prepareStatement(sql);
            List<String> keys = Arrays.asList(sql.substring(sql.indexOf("(") + 1, sql.indexOf(")")).split(","));

            for (int i = 0; i < keys.size(); i++) {
                stmt.setObject(i + 1, param.get(keys.get(i).trim()));
            }

            // 执行
            stmt.executeUpdate();
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage());
        } finally {
            try {
                stmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e.getMessage());
            }
        }
    }

    /***
     * String数组转 ('1', '2', '3')
     */
    private String getInParam(com.apestech.framework.json.JSONArray param){
        String str = param.stream().reduce("",(a, b) -> {
            if (a.equals("")) return "'" + b + "'";
            return a + ", '" + b + "'";
        },String::concat);

        return "(" + str + ")";
    }

    /**
     * 只获取ID用于查询
     */
    private com.apestech.framework.json.JSONArray format(com.apestech.framework.json.JSONArray param) throws Exception{
        com.apestech.framework.json.JSONArray result = new com.apestech.framework.json.JSONArray();
        for (int i = 0; i < param.size(); i++) {
            result.add(param.getJSONObject(i).get("id"));
        }
        return result;
    }

    /***
     * 检查插入的数字类型字段
     * 初始化数字字段 默认值0
     */
    public void checkField(JSONObject param, String... items){
        for (String field : items) {
            if (!param.containsKey(field)){
                param.put(field,0);
            }else if (param.get(field) == null){
                param.put(field,0);
            }
        }
    }

    /***
     * 用于验证参数不能为空
     */
    private void checkNull(JSONObject param, String... items){
        for (String field : items) {
            if (param.get(field) == null) throw new RuntimeException("参数：" + field + "不能为空");
        }
    }


    /** 分销销售提成负单 判断是否存在 /*/
    public JSONArray existXSTCFXFD(JSONObject param){
        checkNull(param,"TJRQ","RETAIL_ORDER_ID","DEPT_ID","PRODUCT_ID","SOURCE");

        String sql = " SELECT COUNT(1) CZBJ FROM TC_XSETC T WHERE TRUNC(T.TJRQ) = TRUNC(:TJRQ) AND T.RETAIL_ORDER_ID = :RETAIL_ORDER_ID " +
                "AND T.DEPT_ID = :DEPT_ID AND T.PRODUCT_ID = :PRODUCT_ID AND T.SOURCE = :SOURCE ";
        return toJSONArray(this.find(sql, param));
    }


    /** 分销单据施工提成负单 判断是否存在 */
    public JSONArray existDJSGTCFXFD(JSONObject param){
        checkNull(param,"TJRQ","RETAIL_ORDER_ID","DEPT_ID","PRODUCT_ID","SERVICE_PLAN");

        String sql = "SELECT COUNT(1) CZBJ FROM TC_DJSGTC T WHERE TRUNC(T.TJRQ) = TRUNC(:TJRQ) AND T.RETAIL_ORDER_ID = :RETAIL_ORDER_ID" +
                " AND T.DEPT_ID = :DEPT_ID AND T.PRODUCT_ID = :PRODUCT_ID AND T.SERVICE_PLAN = :SERVICE_PLAN";
        return toJSONArray(this.find(sql, param));
    }

}
